Why doesn't the Parameter Query work with the input from the Access Form?

Parameter Query
Please see Embed FIle for snapshot of a Parameter Access Query. I have a form that supplies the input to this query.
All the fields on the Form ARE NOT required to be filled in except the Date Range. The other fields on the form are
Filtered fields. If the user Does NOT select any filter field but just selects the Date Range, the design of the Parameter Query works.
If the user selects the Date Range and filters any of the other fields, the Parameter Query does not work correctly.
Is the parameter query not the best way to return the correct information based on what the user does on the form?

I am not a programmer. Is there a better way to do this?
ES-ComponentsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Where you reference a form field, you need to do:

=Forms![CustomerSales]![myControlNameHere] or  Forms![CustomerSales]![myControlNameHere] is null

 When you save the query, Access will reformat that, so get everything right the first time.

You also need to repeat the Between all the way down the rows.

Right now, your saying:

1. If must be between these dates.

or

2. Field17 must = <value>

or

3. Field3 must = <value>

and so on

 all as seperate checks.

The rows are OR conditions, and across are AND's.

What you want is:

Between date and date  and (Field17 = Value or is null)

and you should rename your fields, or your going to find this tough to work with.

Jim.
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
ste5anSenior DeveloperCommented:
You must replicate the date range condition in the date column on each condition line where you have another condition on other columns.
0
ES-ComponentsAuthor Commented:
Thank you both for your quick response and help. The "Is Null" part was what what I needed to understand better.
Thank you both for giving me the examples.
The Form works perfect now and we are able to Print, Send Email, and Export the results of the Form Filters!!!

Rick
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I am not a programmer. Is there a better way to do this?>>

Just one additional comment; the answer to that question was yes, but you need to be a programmer to use it, so for the present, stick with the method you have.

But to further your understanding a bit, when you work with a query, what your really working with is an SQL statement:


  SELECT   <field>  FROM  <table>  WHERE <criteria>

 This is what all relational database systems work with under the covers.   You can see this SQL when your in the query designer by selecting "SQL View".

 Right now, Access is doing the checks of what is and is not filled in by you including the "IS NULL".     It's also having to grab the values from the form controls.

  But it would be more efficient if those checks were only included if something was filled in and you got the specific value.

  With code, you can build up an SQL statement "on the fly" and then execute it.  That would look something like this:

  strSQLWhere = ""

  If NZ(Forms![myForm]![myControl],"")<>"" then
      strSQLWhere = strSQLWhere " AND " [Field] = " & Forms![myForm]![myControl]
  End If

So were saying if something is in the control, add it to the string variable strSQLWhere, and you'd repeat that for each condition.   In the end, you have a very efficient SQL statement to execute.

  But the method your using now is fine and will get the job done.  The only hassle is that Access reformat's the query.   You'll notice if you open it again that Access has placed all the control references in a column:

Forms![myForm]![myControl]

with a multitude of IS NULL checks below it.    Note that criteria grid has gotten really complicated to follow.

So the downside for a query like this is that if you need to modify it,  it's best to erase those columns on the end, all the criteria, and start again with the criteria.  Otherwise you'll spend a lot of time trying to get all those checks right.

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