Conditional Criteria

[Nathan C. Lee]

I'd like to create criteria in a query that only takes effect if the field
where it's looking is not null.

I'm making a parameter query, where the user selects the query's parameters
in a dialog box. I'd like it if the query didn't filter out anything if the
particular field is null. For example, I have a "PO Number" field and
"Purchaser" field. If both are filled out, the query will only return
records where both are matched. If "PO Number" is left empty, then all
records matching "Purchaser" are returned.

Thanks in advance,
Nathan

 

[Rick]

you need to add &"*" to your criteria. For example:

Like [Forms]![ReportParameters]![EnterSupervisorID] & "*"

Would make the supervisor match if entered, or would pull all if left blank.

Rick


I'd like to create criteria in a query that only takes effect if the field
where it's looking is not null.

I'm making a parameter query, where the user selects the query's parameters
in a dialog box. I'd like it if the query didn't filter out anything if the
particular field is null. For example, I have a "PO Number" field and
"Purchaser" field. If both are filled out, the query will only return
records where both are matched. If "PO Number" is left empty, then all
records matching "Purchaser" are returned.

Thanks in advance,
Nathan

 

[John Spencer (MVP)]

You can use the LIKE operator and the NZ function in conjunction with your
parameter. This works as long as your field ALWAYS has a value and is a TEXT
field. Numbers and Dates can be handled with similar but different logic.

LIKE Nz([Your Parameter],"*")

However, if the field contains nulls and you want the records despite that, you
can enter the following criteria in the criteria row.

[Your Parameter] or [Your Parameter] is Null

NOTE: Access will rearrange this when you save the query. If you have too many
of these type statements and too many other criteria, you will end up with a
"QUERY TOO COMPLEX" error and the query won't run.