Not necessarily. There is a technique you can use that will solve that
problem; however, I need to know if the drop downs are on the form you are
filtering, or are you making selection that will be used to open a new form.
The technique is a little different depending on what you want to do.
However, here is the basic principle. You will need a function that will
build the criteria for you. the idea is that you only add to the criteria
for the combos that have a value.
Function BuildWhereString() As String
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "[FirstField] = '" & Me.Combo1 & "'"
End If
If Not IsNull(Me.Combo2) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[SecondField] = " & Me.Combo2
End If
If Not IsNull(Me.Combo3) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[SecondField] = " & Me.Combo3
End If
If Not IsNull(Me.Combo4) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[SecondField] = " & Me.Combo4
End If
If Not IsNull(Me.Combo5) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "[SecondField] = " & Me.Combo5
End If
BuildWhereString = strWhere
End Function
It will return a string that would be the same as an SQL WHERE clause
without the word WHERE, which is what you would need in any situation. Note,
you will need to adjust the code to use real names and correct syntax for the
data type.
--
Dave Hargis, Microsoft Access MVP
Supe said:
I have a form set up that uses a query as a records source. I have 5
different drop downs to choose from with the criteria in the query set up as
like below to link to the form
=[Forms]![Selected Criteria Form]![Year]
By setting up this way, you need to select something from all 5 drop down
lists for it to work. How would I set it up where I could select anywhere
from 1 to 5 of the drop down options?
