Access query/search help

[woro2006]

Hi

I have a query that depends on the input (several parameters) from a form. The form has a list object that shows the results of the query. For example, when I type in "s" in last name and "b" in city, it shows everybody whose last name starts with s and city name that starts with b. The problem is that it excludes all the records with blank cells in either last name or first name or city. In another words, the list is already pre-filtered with records that contain all three fields (city and first name and last name). For example, the record (Last name: Mosby, First name: Ted, City = blank) will be excluded from getting displayed because it contains no characters. Can someone help me out with this issue? I would like to include those people on the search even though their city is not entered in the table. With the query parameter empty, I would like to show a very raw list (a list that is not filtered at all).


The Query is built as follow:
The criteria for the field "Last Name" is
Like [forms]![simform].[lastnamesearch] & "*"

The criteria for the field "First name" is
Like [forms]![simform].[firstnamesearch] & "*"

The criteria for the field "City" is
Like [forms]![simform].[city] & "*"

 

[dhookom]

You can change your fields to something like:

[LastName] & ""
Like [forms]![simform].[lastnamesearch] & "*"

[Firstname] & ""
Like [forms]![simform].[firstnamesearch] & "*"

[City] & ""
Like [forms]![simform].[city] & "*"


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]

 

[woro2006]

Hi Duane,

Merry Christmas!

How come your query works? I don't understand that. Can you explain to me what went wrong with mine (why it exclude blanks) and why yours works (include the blanks) in plain English?

Thanks ahead.

 

[dhookom]

A Null value is basically unknown. You can't compare anything to unknown. For instance, is "St*" like unknown? There is no way to tell if unknown is like or equal to anything else.

My solution converts nulls to a zero length string by concatenating a "" to it. This has no effect on non-null values. Null values become a string and can be compared using = or like.

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]