How to make multiple criteria query when sometimes the criteria can be blank

[Phlee]

Hi,



I am pretty new to Access (first project)

I know how to make a query by form where I can query using selected information on a form.

I have 2 specific questions:

1. How do I do this with multiple criteria? Also, if I add multiple criteria, is it okay if sometimes some of those criteria are left blank?!

2. How can I make a date range a criteria?

Thanks!

[boblarson]

I wouldn't put the criteria on the query. I would put it in the event which opens the form (like the click event of a button), and you can use it like this (which will just ignore it if blank):

Dim strWhere As String
 
' For Numeric Datatypes
If Len(Me.Control1NameHere & vbNullString) > 0 Then
   strWhere = "[Field1NameHere] = " & Me.Control1NameHere & " AND "  ' this assumes a numeric datatype otherwise Chr(34) is needed to add double-quote)
End If
 
' For text datatypes
If Len(Me.Control2NameHere & vbNullString) > 0 Then
   strWhere = strWhere & "[Field2NameHere] = " & Chr(34) & Me.Control2NameHere & Chr(34) & " AND "  ' this example shows that Field2NameHere is a text datatype so we added the Chr(34) code
 
' For date range
If Len(Me.Control3NameHere & vbNullString) > 0 And Len(Me.Control4NameHere & vbNullString) > 0 Then
   strWhere = strWhere & "[DateField] Between #" & Format(Me.Control3NameHere, "mm\/dd\/yyyy") & "# AND #" & Format(Me.Control4NameHere, "mm\/dd\/yyyy") & " AND "  ' Dates use # for delimiters and the format is set to make sure regional settings don't affect the SQL
End If
 
' If the start date is left blank
If Len(Me.Control3NameHere & vbNullString) = 0 And Len(Me.Control4NameHere & vbNullString) > 0 Then
   strWhere = strWhere & "[DateField] <= #" & Format(Me.Control4NameHere, "mm\/dd\/yyyy") & " AND "
End If
 
' If the ending date is left blank
If Len(Me.Control3NameHere & vbNullString) > 0 And Len(Me.Control4NameHere & vbNullString) = 0 Then
   strWhere = strWhere & "[DateField] >= #" & Format(Me.Control3NameHere, "mm\/dd\/yyyy")  & " AND "
End If
 
' we remove the ending AND
If strWhere <> vbNullString Then
   strWhere = Left(strWhere, Len(strWhere) - 5)
End If
 
' if filtering the form this code is on then
Me.Filter = strWhere
Me.FilterOn = True
 
' if filtering a different form by opening:
' DoCmd.OpenForm "FormNameHere", WhereCondition:=strWhere