Variable Criteria in Query

[boomtap via AccessMonster.com]

Hi There. I'm trying to make a flexable query so I don't have to make a
bunch of different reports. I have some drop down boxes on a form that the
query looks at to determine what the "Criteria" will be. The query works if
I have all the drop-down boxes populated. I would like to be able to leave
some of the boxes blank if I don't want it factored into the equation. Here
is what I tried in the "Criteria" field of the query:

IIf([Forms]![mainform]![company] Is Not Null,[Forms]![mainform]![company],Is
Not Null)

So, if the drop-down box is populated, it uses the value in the box <--- thay
part works. I thought with the "is not null" command if it wasn't populated,
it would just show them all.

Any suggestions? I have to get this done for my boss pretty quickly.

Thanks.
Mike

 

[Guest]

Like "*" & [Forms]![mainform]![company] & "*"

The problem with the above is that if you have a company A it could bring in
Amce, Bach, Aba...

 

[John Spencer]

[Forms]![mainform]![company] OR [Forms]![mainform]![company] IS Null

Access will reformat that when you save the query, but it should work for
you.

If you do that for several fields the query will become too complex and
Access will error.

In that case, you probably want to look at building the query criteria using
VBA.

 

[Guest]

I would approach it a little differently. I would put no parameters in the
query and use the Where argument of the OpenReport method to do the
filtering. This way, you avoid the ambiquities of the "Like" (Which I really
don't like):

Private Function BuildWhere() As String
Dim strWhere As String

If Not IsNull(Me.FirstCombo) Then
strWhere = "[FirstTableField] = '" & Me.FirstCombo & "'"
End If

If Not IsNull(Me.SecondCombo) Then
strWhere = strWhere & AddAnd(strWhere)
strWhere = strWhere & "[SecondTableField] = '" & Me.SecondCombo & "'"
End If

If Not IsNull(Me.LastCombo) Then
strWhere = strWhere & AddAnd(strWhere)
strWhere = strWhere & "[LastTableField] = '" & Me.LastCombo & "'"
End If

BuildWhere = strWhere
End Function