By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,285 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

Access Critereia Query - with blank fields and search parameters

P: 3
Hi everyone...

I'm a high school student working on an Access project for a summer internship. I needed your assistance in writing a criteria for a select query.

Table1 has the following fields:
ID, First_Name, Last_Name, Org, Email, Status

Only "Email" is mandatory, ID is autonumber, the rest are optional.

I have to create a query that will allow users to search the table with any of the fields above. A user may search with only one field, e.g. all users where "org" = "YMCA"

Presently, I am using the similar criteria for all the fields:

Like "*" & [Forms]![Search]![txt_FirstName] & "*"

The problem occurs when, for example a record exists with the following -
First_Name = Null or Blank
Last_Name = "Smith"

If you search for "Smith" in Last_Name, then the record does not show up, because First_Name in the record is blank.

How can I alter the criteria for it do search correctly?

I already tried:
Like "*" & [Forms]![Search]![txt_FirstName] & "*" & ""

Jul 19 '07 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 12,366
Indeed the Like operator ignores Null fields. However, it will return empty strings. What you need to do is convert a Null to an empty string before using Like. You'll want to take a look at the Nz() function.
Jul 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.