Parameters only show records w/data in parameter field? 2

[winston01]

Thank you for reading.

When you create a parameter in a query, and run the query, it will not pull up a record if the field that has the parameter set to it is blank?

I do not want to make all the parameter fields required fields. Is there anyway to work around this so that I can leave fields blank and they will show in my query result even with a parameter set to that field?

Thank you.

 

[dhookom]

As you have discovered, null values don't match any criteria. You can convert null field values to a string by concatenating a zero-length-string to the field. Your query sql might look like:
[tt][blue]
WHERE Address2 & "" Like "*" & [Enter street] & "*"
[/blue][/tt]

This will match all records if Address2 is null and the user doesn't enter a street.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another way:
WHERE (your Field = [Enter value] Or [Enter value] Is Null)
Yet another way:
WHERE (your Field = [Enter value] Or Trim([Enter value] & '') = '')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[winston01]

This worked great. Thank you!