Question Something wrong with my SQL for running query

[josephbupe]

Hi,

I am having a tough time figuring out what is wrong with my project on running queries with the SQL statements.

The function is imported into my project from somewhere, modified a bit and now looks like this:

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim strField As String [COLOR=Green]'for the date field[/COLOR]

    varWhere = Null [COLOR=Green] ' Main filter[/COLOR]
    [COLOR=Green]'..............................................................................................................................
    [/COLOR]
[COLOR=Green]' Const conDateFormat = "\#mm\/dd\/yyyy\#"[/COLOR]
    Const conDateFormat = "\#dd\/mm\/yyyy\#"

[COLOR=Green]    ' strReport = "R_Programmes"[/COLOR]
    strField = "[Incident_Date]"
    varWhere = ""
    
   [COLOR=Green] ' ================================================================================
    ' CHECK FOR DATE RANGE
    ' ================================================================================[/COLOR]
    If IsNull(Me.txtIncidentStartDate) Then
        If Not IsNull(Me.txtIncidentEndDate) Then [COLOR=Green] 'End date, but no start.[/COLOR]
            varWhere = "(" & strField & " <= " & Format(Me.txtIncidentEndDate, conDateFormat) & ") "
        Else
      [COLOR=Green]      'neither start nor end dates, do nothing to varWhere[/COLOR]
        End If
    Else
        If IsNull(Me.txtIncidentEndDate) Then[COLOR=Green]  'Start date, but no End.[/COLOR]
            varWhere = "(" & strField & " >= " & Format(Me.txtIncidentStartDate, conDateFormat) & ") "
        Else [COLOR=Green]'Both start and end dates present.[/COLOR]
            varWhere = "(" & strField & " Between " & Format(Me.txtIncidentStartDate, conDateFormat) _
            & " AND " & Format(Me.txtIncidentEndDate, conDateFormat) & ")"
        End If
    End If
      
    [COLOR=Green]      ' Check for LIKE Other Names[/COLOR]
    If Me.txtIncident <> "" Then
        varWhere = varWhere & "[Incident] LIKE '*" & Me.txtIncident & "*' AND "
    End If
          
[COLOR=Green]     ' Check for LIKE Ref[/COLOR]
    If Me.lstStatus <> "" Then
        varWhere = varWhere & "[Status] LIKE '*" & Me.lstStatus & "*' AND "
    End If

[COLOR=Green]    ' Check for Incident Type[/COLOR]
    If Me.lstIncidentType <> "" Then
        varWhere = varWhere & "[Type] Like '*" & Me.lstIncidentType & "*' AND "
    End If
    
[COLOR=Green]    ' Check for Incident Type[/COLOR]
    If Me.lstPurpose <> "" Then
        varWhere = varWhere & "[Purpose] Like '*" & Me.lstPurpose & "*' AND "
    End If
             
[COLOR=Green]    ' Check if there is a filter to return...[/COLOR]
    If IsNull(varWhere) Then
        varWhere = ""
        
    Else
        varWhere = "WHERE " & varWhere
        
   [COLOR=Green]     ' strip off last "AND" in the filter[/COLOR]
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If

    Debug.Print varWhere
    
    BuildFilter = varWhere
      
End Function

And the search code:

Private Sub cmdSearchIncident_Click()
    
    ' Update the record source
    Me.lstIncidents.RowSource = "SELECT * FROM Query_Incidents " & BuildFilter
  
[COLOR=Green]    ' Requery the form[/COLOR]
    Me.lstIncidents.Requery

End Sub

The problem is that the criterias in the two red list boxes cant return the results even when I have data to return in the columns TYPE and PURPOSE in the lstIncidents list box above.

Please, see attached sample for easy reference and help.

I will appreciate your help.

Joseph

 

[spikepl]

Instead of "figuring" step through your code at runtime using the debugger and check what is going on in there. The recipe for how to do that is given here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429 in the section Debugging Tips.

 

[josephbupe]

Instead of "figuring" step through your code at runtime using the debugger and check what is going on in there. The recipe for how to do that is given here: http://www.access-programmers.co.uk/forums/showthread.php?t=149429 in the section Debugging Tips.

Thank you.

On debug, the immediate window return the following response:

WHERE [Status] LIKE '*Active*' AND [Type] Like '*2*' AND [Purpose] Like '*5*'

If i run query with only the value list (Active, Completed), I get correct results. But if I run query on the other two criteria (Type) , (Purpose), nothing is returned. The numbers 2 and 5 returned in the immediate window are as stored in the table. But I need to return the strings equivalent to the numbers stored in the table, since am using a query as the row source for the listbox.

I am missing something in the punctuation of the WHERE clause???

Joseph:banghead:

 

[DavidAtWork]

What are the data types for 'Type' and 'Purpose' that you're trying to query as in your line above AND [Type] Like '*2*' AND [Purpose] Like '*5*'
David