Hello all,

I am not really familiar with the terminology behind what I am doing, so I will attempt to describe in newbie language. I am setting up my queries so that parameters are determined by a cover form. End user can choose date range, department by entering in a form. Everything is working well but I have two simple questions. If a field such as 'Choose Department' is left blank, how do I get my query to provide all department records. Right now, in this instance the query is blank.

Also,

I would like the ability to be able to choose 2 or more departments at once in the query. I have set up a list box with multiselect active, but nothing seems to happen. Please help me. Thanks in advance.

Pablo

Pablo,

For the first part, you are probably basing your
form on a query. You can use two columns for
the criteria:

Forms![YourForm]![YourSearchField] and

Expr: IsNull(Forms![YourForm]![YourSearchField])

As you look horizontally on the query grid
you want to see the criteria "staircased".
This will give (In SQL view):


Select *
From YourTable
Where (SomeField = Forms![YourForm]![YourSearchField] Or
IsNull(Forms![YourForm]![YourSearchField])


hth,
Wayne

Wayne,

I just went to my query design and added a duplicate field to add the statement in, For some reason it does not work. After I try it, the staircase is gone and both statements are under one line. What am I doing wrong? Thanks. Paul

Pablo,

It's not really a duplicate field. Note the "Expr:". The new
field has a new name and once you get it tested, it doesn't
even have to be visible.

Wayne

Wayne,

I must be a dumb***, cause I still can't get it to work. I went into my query design and put the expression in a new column and I don't get any records when I run the query. The other aspect still is working in that if I enter a value into the form I see the corresponding records. I am hoping you may have an example? Thanks for your patience.

Paul

Paul,

If you can:

Tools --> Utilities --> Compact/Repair databse
ZIP it
Attach to post.

Then we'll have an example.

Wayne

Unfortunately can't post my database as it contains confidential information. Still would appreciate help.

Paul,

Threw this together, experiment with it.

Wayne

Wayne,

Forgot to mention that my agency is stuck in the dark ages and is using Access 97'. Unfortunately, I can not view the attachment. Could you please save under a different format? Thanks. Paul

Paul,

Sure ...

Wayne

Wayne,

Now I am even more confused. I see how your requery field worked on the form, but I don't know how that pertains to my issue. I have a Form that is a report request coversheet. On the form you have a begin date, end date field, department selection field and so on. When you fill in your information on that form and hit a report button, it previews a report based off of a query which utilizes the fields on the request coversheet. I don't know if I am looking at your information correctly. Thanks again for all of your assistance. Paul

Paul,

Unless I'm missing something here, we're talking about the
same thing. You can have a Dialog box that comes up where
the user can enter their desired report parameters. Use
combos or whatever.

Then base your report on a query and use the Dialog box
fields as criteria; like in the demo. You must have the form
open when the report runs.

Wayne