Null in query

[Guest]

I know this has been addressed many times here, but I can't seem to find
exactly what I'm looking for.

I have frmReciepts that is used to enter buyer data into tblReceipts. The
form has multiple fields, including CustomerLast and CustomerFirst. I have a
query that pulls information from tblReceipts via frmSearch where the user
enters search criteria. It's a multiple field criteria query that allows for
all or just some of the fields to have data. I have that part of the query
worked out and it works fine. But, the user would like to be able to type in
a customers name into one text box on frmSearch istead of 2. So, instead of
typing Jane into one text box and Doe into another, they would just type Jane
Doe into one text box. I have concantecated CustomerFirst and CustomerLast
in the query and as long as there is indeed a last name and a first name in
tblReceipts, it works great. But, if say the customer is entered on the
receipt as XYZ company (no first name entered on the receipt) I get no result
in my query.
Any help is appreciated!

 

[Guest]

[FIRST NAME] LIKE Nz(forms!myform!txtFirstName,"*")

If the first name control is null, it will return all first names for the
last name.

 

[John Spencer]

WHAT did you use for the concatenation operator?

IN ACCESS:
FirstName + LastName will return NULL if either of the values is null
FirstName & LastName will treat the null value as if it were a string with
no characters so you will get Either the value in the first name or the
value in the last name.

You can take advantage of that to return First Name Space Last name when
both are there. Or if just last name is there, then last name is returned
without any leading spaces.
Field: SearchThis: ([FirstName] + " ") & [LastName]

If you had used
FirstName & " " & LastName then if no first name exists the expression is
returning SPACE followed by the Last Name value

In that case you could use the Trim function as an alternate way to get rid
of the pesky space where you don't want it.
Field: FullName: TRIM([FirstName ] & " " & [LastName])

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..