Querie Search for Combo Boxes

[Doug Bodenstab]

Hello

I need to run a report that calls on data from three combo boxes in
a form for three diffferent fields in a querie. The problem is sometimes the
user will want to run all data for a certain field.

For Example here are 2 different searches where Search #2 calls all records
for that field.

Search Field Company JobName Task
#1 Coastal Building Walls
#2 Coastal Building All
Combo12 Combo13 Combo14



If I leave the combo blank it returns nothing

I tried diffenent methods


When I use a querie search such as
[Forms]![Task Pop Up]![Combo14]

or
If I use an if statement such as

IIf([Forms]![Task Job Pop Up]![Combo14]="All",(

.[Task]) Like "" &
"*",[Forms]![Task Job Pop Up]![Combo14])

This iif statememnt works but it fails to search for anything

Also if I use a VB search such as

stLinkCriteria = "[Task]=" & "'" & Me![Combo14] & "'"

stDocName = "Task Report"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I get nothing if I leave blank.

Your help is greatley appricated.

Doug

 

[Ken Snell \(MVP\)]

The "trick" is to use two tests on each combo box:

Forms]![Task Pop Up]![Combo14] Or Forms]![Task Pop Up]![Combo14] Is Null

So, an SQL statement might look like this:

SELECT Field1, Field2, Field3
FROM TableName
WHERE (Field1 = Forms]![Task Pop Up]![Combo14] Or
Forms]![Task Pop Up]![Combo14] Is Null) And
(Field2 = Forms]![Task Pop Up]![Combo24] Or
Forms]![Task Pop Up]![Combo24] Is Null) And
(Field3 = Forms]![Task Pop Up]![Combo34] Or
Forms]![Task Pop Up]![Combo34] Is Null);