Question Help connect date range with other criterias in SQL

[josephbupe]

Hi,

I have the following function which works well except when I want to run a search using date range and any other criteria on another text/listbox or combo box.

This is work i get in the immediate window (Where Status, TypeID, PurposeID are the other criterias used with the date range.):

WHERE ([Incident_Date] Between #02/06/2012# AND #02/06/2012#)[Status]  LIKE '*Active*' AND [TypeID] Like '*1*' AND [PurposeID] Like '*2*'

And the Build Function is as follows:

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim strField As String 'for the date field

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

      strField = "[Incident_Date]"
    varWhere = ""
    
   [COLOR=DarkGreen] ' ================================================================================
    ' CHECK FOR DATE RANGE
    ' ================================================================================[/COLOR]
    If IsNull(Me.txtIncidentStartDate) Then
        If Not IsNull(Me.txtIncidentEndDate) Then [COLOR=DarkGreen] 'End date, but no start.[/COLOR]
            varWhere = "(" & strField & " <= " & Format(Me.txtIncidentEndDate, conDateFormat) & ") "
        Else
[COLOR=DarkGreen]            'neither start nor end dates, do nothing to varWhere[/COLOR]
        End If
    Else
        If IsNull(Me.txtIncidentEndDate) Then [COLOR=DarkGreen] 'Start date, but no End.[/COLOR]
            varWhere = "(" & strField & " >= " & Format(Me.txtIncidentStartDate, conDateFormat) & ") "
        Else [COLOR=DarkGreen]'Both start and end dates present.[/COLOR]
            varWhere = "(" & strField & " Between " & Format(Me.txtIncidentStartDate, conDateFormat) _
            & " AND " & Format(Me.txtIncidentEndDate, conDateFormat) & ")"
        End If

   End If

[COLOR=DarkGreen]'=========[COLOR=Red]I need help here to connect between the DATE RANGE criteria above and the rest below [/COLOR]========[/COLOR]

If Me.txtIncident <> "" Then
        varWhere = varWhere & "[IncidentID] = " & Me.txtIncident & " AND "
    End If
          
  [COLOR=DarkGreen]   ' Check for LIKE Ref[/COLOR]
    If Me.lstStatus <> "" Then
        varWhere = varWhere & "[Status] = '" & Me.lstStatus & "' AND "
    End If
    
[COLOR=DarkGreen]    ' Check for Incident Type[/COLOR]
    If Me.lstIncidentType > 0 Then
        varWhere = varWhere & "[TypeID] = " & Me.lstIncidentType & " AND "
    End If

[COLOR=DarkGreen]' ============== Check if there is a filter to return===============================[/COLOR]
    If IsNull(varWhere) Then
        varWhere = ""
        
    Else
        varWhere = "WHERE " & varWhere
        
       [COLOR=DarkGreen] ' 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

Your help is appreciated.

Joseph

 

[pr2-eugin]

Hello Joseph, Try this as your date range builder.. Not tested.. Let me know the outcome..

If IsNull(Me.txtIncidentStartDate) Then
    If Not IsNull(Me.txtIncidentEndDate) Then  'End date, but no start.
        varWhere = "(" & strField & " <= " & Format(Me.txtIncidentEndDate, conDateFormat) & ") AND "
    Else
        'neither start nor end dates, do nothing to varWhere
    End If
Else
    If IsNull(Me.txtIncidentEndDate) Then  'Start date, but no End.
        varWhere = "(" & strField & " >= " & Format(Me.txtIncidentStartDate, conDateFormat) & ") AND "
    Else 'Both start and end dates present.
        varWhere = "(" & strField & " Between " & Format(Me.txtIncidentStartDate, conDateFormat) _
                    & " AND " & Format(Me.txtIncidentEndDate, conDateFormat) & ") AND "
    End If
End If

 

[josephbupe]

Hi Eugin,

I got this in the immediate window:

WHERE ([Incident_Date] Between #01/06/2012# AND #02/06/2012#) AND [TypeID] Like 2 AND [PurposeID] Like 4

I think your code is working. I will try to populate my database with more data and try to run as many queries and possible to be sure enough.

Thank you so much for your continued assistance.

Stay well.

Joseph

 

[pr2-eugin]

You are welcome Joseph.. Keep me posted..