Query criteria to select all records when no value is selected from combo box

I am trying to build a form that provides the query perameters for a report. On the parameter form, I build a combo box that selects from a list of offices and entered the following criteria in the query: [Forms]![Date Range]![Office] And <=[Forms]![Date Range]![Office].
The query returns correctly if an office is selected. However, I want the query to return all the records for all the offices if no value is selected from the combo box.  How can I change the query criteria to make this happen?

Please note that I'm a rookie!
Thanks for your Help!
thutchinsonAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
First off, I'm not quite following this logic ... can you say in words what you are after here ?

[Forms]![Date Range]![Office] And <=[Forms]![Date Range]![Office].
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Typically, it would be something like this:

SELECT Table1.*
FROM Table1
WHERE (((Table1.YourDate)=IIf(IsNull([Forms]![Date Range]![Office]),[YourDate],[Forms]![Date Range]![Office])));

So, if the field on your form is blank, all records are returned - first part of IIF, otherwise, 'YourDate' must match the date in the control on the form.

mx
0
thutchinsonAuthor Commented:
I am trying to create a form that accepts user-input query parameters.  That way, the user can enter the criteria from the form and run the query. The name of the form is "Date Range".
When the query is run, it looks to criteria in the form to return the records according to the form parameters.   Everything is working fine except that I need the query to return records from all the offices unless a specific office is selected from a combo box listing the offices on the "Date Range" parameter form.
I gave you the criteria above that is entered in the query.  The query doesn't run unless an office is selected from the parameter form (because no records meet the criteria of "".  
I need the query to return all the records if nothing is selected.  In other words, RETURN ALL RECORDS.  I tried entering *.* as default value.  No joy.  The criteria expression needs to be changed somehow.
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
See my 2nd post.

Yes ... I understand all of that.  I was just wonder about the expression you posted.

What field in your query is this criteria going to be on?

mx
0
thutchinsonAuthor Commented:
Sorry, MX.  I got your second post after I released the comment.

The field name is "OfficeDesc".

To be more clear, the parameter form named "Date Range" also has "StartDate" and "EndDate" input text boxes.  The query field "TakenDate" criteria is >=[Forms]![Date Range]![StartDate] And <=[Forms]![Date Range]![EndDate].  I want to do the same thing with a combo box on the field "OfficeDesc".
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, for the combo alone - it would be this.

SELECT Table1.*
FROM Table1
WHERE (((Table1.OfficeDesc)=IIf(IsNull([Forms]![Date Range]![Office]),[OfficeDesc],[Forms]![Date Range]![Office])));

Try this first w/o the date range criteria - to confirm all records or just a combo match ...
Substitute your Query name for Table1 above
mx
0
thutchinsonAuthor Commented:

The logic sounds good but I don't know what to do with it.
The name of the query is "qrytblSales_IPTV_TakenDate".  Do I add a Totals line and change the Total line in the OfficeDesc field to "Where"? and then insert your code?
This is what I did but doesn't seem right.  See attached. Parameter-query-with-WHERE.pdf
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

ok ... the only thing you want it the Criteria cell is this:

IIf(IsNull([Forms]![Date Range]![Office]),[OfficeDesc],[Forms]![Date Range]![Office])

mx
0

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
thutchinsonAuthor Commented:
MX, you're the best.  That looks exactly what I need.

 There's a problem with syntax, though.  I'm still getting that invalid (dot) or parenthesis error. I can't find it. Can you see anything wrong?  Same error as previous image sent.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Do you have exactly this in the WHERE clause criteria for OfficeDesc

IIf(IsNull([Forms]![Date Range]![Office]), [OfficeDesc], [Forms]![Date Range]![Office])
?

If so, then can you upload the db?
0
thutchinsonAuthor Commented:
I copied and pasted from you.  Here's another image.
I'll need some time to figure out how to upload a peice of this very large db. Parameter-query-with-WHERE2.pdf
0
thutchinsonAuthor Commented:
Sorry, Syntax is good.  There were remnants of the previous expression in the line.
I'm testing now.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
so ... working ?
0
thutchinsonAuthor Commented:
Yes, working.  Thanks so much MX.  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is a slightly different approach, which I think is more efficient, because if the is an Index on OfficeDesc, it can still be used, whereas in the first approach ... I don't think it can with the IIF.

mx
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.