Combo Box Nulls

[Guest]

Hello,

I have 3 combo box on a user 'Rpt' form. The combo boxes enable the user to
filter the report.
How do I handle Null values? If the user does not enter a selection into
the combo box - how do I pass that information down through the code to print
out everything.

I have the following

Here is my current statement - assuming the user has entered a criteria in
each, it works.

strWhere = "[Country] = '" & Me!cmb_Country & "' and [Region] = '" &
Me!cmb_Region & "' and [ClientSector] = '" & Me!cmb_Sector & "'"

So I need a simple way to check each combo box, and if its empty assign *

Thanks
AC

 

[Allen Browne]

Only add that part to the WHERE clause if the combo is not null.

This example shows how to set up as many combos as you need, but only
incorporating the ones that have a value. It tacks the " AND " to the end of
each one, and then chops off the trailing 5 characters at the end.

Dim strWhere As string
Dim lngLen As Long

If Not IsNull(Me.cmb_Country) Then
strWhere = strWhere & "([Country] = """ & Me.cmb_Country & """) AND "
End If

If Not IsNull(Me.cmb_Region) Then
strWHere = strWhere & "([Region] = """ & Me.cmb_Region & """) AND "
End If

'etc for other combos.

lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

 

[David C. Holley]

I would create code that builds the WHERE statement and then passes that
to the report in the DoCmd.OpenReport statement. This will allow you to
determine wether or not the value is NULL or not. It might get a bit
tricky since you'll have to factor in when to add a 'AND' statement to
the WHERE, but its not undoable.