Parameter query to pull records with Blank value 2

[mdlaugh1]

I have a parameter query to prompt for "Enter the Building or * for All" as: Like [Enter the BUILDING or * for all] & "*"

Problem is that some records do not have a value in Building field and the query is ignoring those records. How do I structure the query to allow for entering one building name or listing all records including those with no building?

I found previous post similar but could not get it to work. Backend is Oracle 9i using Access 2003 if that matters.

thanks!!

 

[mdlaugh1]

Well... I possibly just made it work.. I added 'Or IS Null" to the statement as follows:

Like [Enter the BUILDING or * for all] & "*" Or Is Null

This appears to work. Have I unintentionally changed query to give me incorrect result?

thank you..

 

[Golom]

No. The result should be what you want. The query designer string translates to

WHERE [SomeField] Like [Enter the BUILDING or * for ALL] & "*" Or [SomeField] Is Null

which will retrieve

- All records where [SomeField] starts with the entered character(s)

or
- All non-NULL records in the table if "*" is entered

PLUS
all records where [SomeField] IS NULL.

 

[mdlaugh1]

Thank you Golom! The blank records are being captured now.

To clarify one thing you said related to the "starts with entered character(s)...

If the data contains City Hall, Citizens Center, Facilities....

then if the user enters Ci at the prompt should it list both city Hall and Citizens Center?.. for some reason I thought the query was not pulling records if the full name was not entered.

Appreciate your help.

 

[lespaul]

Like works as follows:

AC* - finds all records that start with AC
Account
Access

*AC - finds all records that end with AC
Lilac
Prozac

*AC* - Finds all records that contain AC
Account
Access
Back
Pack
Lilac
Prozac

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[mdlaugh1]

Thanks Leslie! It's working now.. probably just a typo when I was trying to get the blank records to pull.