using the IIF statement in query criteria.

[Homegrownandy]

This is all relative to one form "search_timesheets" and one table "Timesheets".



I am trying to have a form with a list of combo boxes that will each list data from one of the fields in the table Timesheets. The user will use these combo boxes to filter data on a query that will then bring up a form with the results.

Below is my attempt:

IIf([Forms]![search_timesheets]![staff_name_combo] Is Null,([Timesheets].[Name_Updated])="*" Or Is Null,([Timesheets].[Name_Updated])=[Forms]![search_timesheets]![staff_name_combo])

This isn't working. To explain what I want this to do:

If nothing is in a combo box then the query doesnt need to use that combo box as a filter. I want it to ignore it. So what I've attempted Is for it to do a 'wildcard' search (I believe thats what its called). But, If something is selected (not null) then I want that value to be used.

Im not having much luck at the moment and im hoping someone can point me in the right direction. Thanks!

If the combo box is null

[ranman256]

1. the test for null is: iif( ISNULL(forms!xxx!timesheet), ..

2. the 2nd part of IIF , ,([Timesheets].[Name_Updated])="*" Or Is Null,,
, must be a value, not a decision

[Homegrownandy]

Like "*" & [Forms]![search timesheets]![project_name_combo] & "*" Or Is Null

seems to do the job.

[Homegrownandy]

I wanted the second part to also contain null values. But I see what you mean. I have actually done this before I was just trying to make it more complex than it needed to be. Thanks for the reply.