Re: [MS_AccessPros] Will Like '*' return records if the value is null?

 

Doyce-

Close, but no cigar.  First, I recommend against changing the SQL of qrySearch.  That should be a query with no filters that's the Record Source of the form you're opening.  Then the code to build your filter should look like this:

Dim strSearch As String

   If Not IsNull(Me.txtMSDSID.Value) Then

        strSearch = "MSDSID = '" & Me.txtMSDSID & "' "
    End If
    If Not IsNull(Me.txtProductName.Value) Then

        ' If already have part of a predicate

        If Len(strSearch) > 0 Then 

            ' Add an AND

            strSearch = strSearch & " AND "

        End IF
        strSearch = strSearch & "ProductName Like '*" & Me.txtProductName & "*' "
    End If
    If Not IsNull(Me.cboCategory.Value) Then

        ' If already have part of a predicate

        If Len(strSearch) > 0 Then 

            ' Add an AND

            strSearch = strSearch & " AND "

        End If

        strSearch = "Category = " & Me.cboCategory & " "

    End If 

' … and so on.

Then do:

    DoCmd.OpenForm "frmSearchResults", acFormDS, WhereCondition:=strSearch

And for the close, just do:

    DoCmd.Close acForm, Me.Name

No need to do the acSaveNo.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Nov 16, 2016, at 7:36 PM, winberry.doyce@con-way.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John and Duane,

Here's the bigger picture. The database holds Material Safety Data Sheet records. I'm trying to build a form that will search the records using criteria from some unbound text and combo boxes. The user puts whatever they want in the form then presses the search button which should open another form with the filtered results. They can click whatever record they want and it opens another form and displays the record and a copy of the MSDS that has been scanned and stored on a server. Here is the procedure under for the search button. I haven't got the SQL right yet.

Private Sub btnSearch_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySearch")
    Dim lMSDSID As Variant
    Dim sProductName As String
    Dim lCategory As Variant
    Dim sManufacturer As String
    Dim sChemtrac As String
    Dim sPartNo As String

    If IsNull(Me.txtMSDSID.Value) Then
        lMSDSID = " "
    Else
        lMSDSID = " = " & Me.txtMSDSID.Value & " "
    End If
    If IsNull(Me.txtProductName.Value) Then
        sProductName = " Like '*' "
    Else
        sProductName = "= Like *'" & Me.txtProductName.Value & "'* "
    End If
    If IsNull(Me.cboCategory.Value) Then
        lCategory = " Like '*' "
    Else
        lCategory = "= " & Me.cboCategory.Value & " "
    End If
    If IsNull(Me.cboManufacturer.Value) Then
        sManufacturer = " Like '*' "
    Else
        sManufacturer = "= Like *'" & Me.cboManufacturer.Value & "'* "
    End If
    If IsNull(Me.txtChemTrekNo.Value) Then
        sChemtrac = " Like '*' "
    Else
        sChemtrac = "= Like *'" & Me.txtChemTrekNo.Value & "'* "
    End If
    If IsNull(Me.txtPartNo.Value) Then
        sPartNo = " Like '*' "
    Else
        sPartNo = "= Like *'" & Me.txtPartNo.Value & "'* "
    End If

    strSQL = "SELECT qS.* " & _
             "From qS " & _
             "Where qS.MSDSID" & lMSDSID & _
             "AND qS.ProductName" & sProductName & _
             "AND qS.Category" & lCategory & _
             "AND qS.MName" & sManufacturer & _
             "AND qS.ChemTrekNo" & sChemtrac & _
             "AND qS.PartNO" & sPartNo & ";"

    Debug.Print strSQL
    
    qdf.SQL = strSQL
    DoCmd.OpenForm "frmSearchResults", acFormDS
    DoCmd.Close acForm, "frmSearch", acSaveNo

End Sub

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I agree with John. As zou start building your WHERE condition, you should ignore the columns/fields that aren't relevant.

However, you could use this type of syntax:

WHERE qS.MSDSID & '' Like '*'...

Regards,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, November 16, 2016 11:37 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Will Like '*' return records if the value is null?

 

Hello Pros,

I'm building a query on the fly through VBA and if appears that if the values for a field are null, the query won't return the record using criteria Like '*' for the field. Is that a correct? Here is the SQL of the query:

SELECT qS.*
FROM qS
WHERE (((qS.MSDSID) Like '*') AND ((qS.ProductName) Like '*') AND ((qS.Category) Like '*') AND ((qS.MName) Like '*') AND ((qS.ChemTrekNo) Like '*') AND ((qS.PartNO) Like '*'));

In my table there are only 6 records and ChemTrekNo is null for all 6. Using Like '*' as criteria for ChemTrekNo no records are returned. What do I do if I want the records that contain nulls? Wrap it in Nz? What would the SQL look like?

Doyce

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___