How do you limit blank fields from the Is Null criteria of a parameter query?

When I input the criteria "Or Like" [blah, blah] "Is Null," it ends up including records that have a blank value in the field of the chosen parameter. For example, when I choose 4 for Corr, I get records with the Workload Corr and also records where the Workload field is blank. I need the effect of Is Null that gives me all workloads, blank or not, when I want the full report, but if I want to limit the report and select a certain workload, how do I limit the parameter query to not include workloads that are blank? See attached picture. I don't have Or Like, in it right now, since it wasn't working as desired.Parameter query in design view.
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
It is not clear form your question what type of data resides in this field.
What you type in between the square brackets is only what will be displayed to the user.
...It is not really the "Criteria"
So I am not sure what your real criteria is...?

Is your goal here to simple display ALL the records *Excluding Blanks*, if no criteria is entered by the user, ..but if they type in a value to only show those values?

Put another way, ...you never want to see blanks in the query results?

Try something roughly like this:
SELECT Table1.ID, Table1.F1, Table1.F2
FROM Table1
WHERE (((IsNull([F1]))=False) AND (((([Table1].[F1])=[Enter a value] Or [Enter a Value] Is Null))));


Sample is attached.
If this is not what you want, let me know.
Or, the best thing to do would be to post a simple sample database and explain exactly what the user scenarios will be, and what the results should be.

JeffCoachman
Database52.mdb
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Are you sure that the field is actually null?  If it is a text data type, it might contain ZLS and testing for null does not exclude ZLS.
0
David BigelowStaff Operations SpecialistAuthor Commented:
1. What is ZLS?
2. If I use the Or Like, Is Null, criteria with the parameter query, when I select a parameter, for example 4, for Corr, the report displays all the Corr workloads, plus any records that don't have a workload indicated. For some records, there isn't a specific workload affected, so none was ever input in the record. I don't want those displayed in the report. But Or Like Is Null is the only criteria I've found that will allow you to not specify a parameter, in this case a workload, in order to display the whole report. So, I need some way to exclude records that have no workload indicated whenever I select a specific workload parameter.
I'll upload a sample tomorrow.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
zero length string.
Please post your where clause from the query.

I use forms to supply my parameters.  This allows for optional arguments:

Where (fld1 = Forms!myform!fld1 OR forms!myform!fld1 Is Null)
AND
(fld2 = Forms!myform!fld2 OR forms!myform!fld2 Is Null)

This allows null values to also be selected:
Where (fld1 = Forms!myform!fld1 OR forms!myform!fld1 Is Null OR fld1 Is Null)
AND
(fld2 = Forms!myform!fld2 OR forms!myform!fld2 Is Null OR fld2 Is Null)
0
Jeffrey CoachmanMIS LiasonCommented:
David Bigelow,
Did you review my sample and post?
0
David BigelowStaff Operations SpecialistAuthor Commented:
Jeff, this is exactly what I wanted! My mind was numb after working on it and then I was redirected to other tasks for a couple of weeks. Thank you so much!
0
Jeffrey CoachmanMIS LiasonCommented:
ok
great...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.