I have a query that needs to have a string criteria (Employee)
supplied before it returns it’s results.
I also have a form with a combo box of employees, when an employee is
chosen I have code write the chosen value to a temp table. Only one
employee can be specified at a time.
In the Employee field within the query I have a DLookup function
pointing the  temporary table holding the value of the employee
chosen. Please note that the Employee is not actually returned in the
query's results as it is grouped....actually it's a pivot table but I
doubt that is really relevant. Anyway,
This all works fine and dandy. The problem arises when there is no
employee chosen. Instead of showing no records I want to show records
for ALL employees.
No matter what I do I can not get the criteria to evaluate correctly
when there is no value in the temp table. I have tried IIF’s to
evaluate to “IN(Select Employee from Employee)” if the value in the
temp table is NULL. I have even tried to use “Like(“*”) when the value
in the table is null, but nothing works. If I put either one of those
two methods in without the IIF then the query runs fine.
So I guess what I really need to know is how to use conditional
criteria in a query.

---

Request for Question Clarification by mathtalk-ga on 20 Dec 2002 23:35 PST

Hi, bselltiz-ga:

Which version of Access are you working with?

thanks, mathtalk-ga

---

Clarification of Question by bselltiz-ga on 21 Dec 2002 09:00 PST

I am running Access 2002 (XP), under Win Xp Pro OS

You need to add "OR MyLookedUpValue Is Null" to your criteria. The
exact syntax depends on what you currently have. By the way, you may
be doing this the hard way. There are ways to pass a value to a query
without resorting to temporary tables. I recommend you read the Help
files on Parameter queries. Also, if you run the query while the form
with the combo box is still open, you can use the value directly as
your criteria. Take a look at this example of doing this using the
value selected on the form as the parameter, rather than doing a
DLookup:

http://www.mvps.org/access/queries/qry0001.htm

This is an excellect Access FAQ. You may want to take a look around it
while you're  there.
http://www.mvps.org/access/


The logic behind this solution is simple. When you use criteria,
Access looks at each record to see if it matches. The answer is
returned as True or False. If the answer is True, the record is added
to your recordset.

If the criteria is: EmployeeName = "John Doe"
then any record where "John Doe" appears in EmployeeName returns True.

By saying: (EmployeeName = MyLookedUpValue) OR (MyLookedUpValue Is
Null)
you tell Access to return True if *either* expression is True. If your
value is Null, then the second expression is *always* True and you get
all the records, regardless of what the Employee Name is. If your
value is not Null, then the second expression is always False and your
value must match the EmployeeName to return True.
Good luck with your Access project!

- Hammer

---

Request for Answer Clarification by bselltiz-ga on 23 Dec 2002 08:54 PST

The "OR" solution seems to work, only problem is that is slowed my
query down so much that it is no longer usefull. I'll figure out a
workaround or just eliminate the criteria, it's not required.
There were some other things about your answer that I need
clarification on. You mention that I should be able to just reference
an open form for the parameter value. I've done this many times before
but it does not see to work with a crosstab query. It keeps erroring
out saying the form reference is invalid. If I change the query to a
select query but leave everything else the same then the reference
works. I'm just curious if you've run across this before, if you
haven't it's not that important.
I would appreciate it if you could give me some links on how to learn
about parameter queries.
Thanks for your help.
Brian

---

Clarification of Answer by hammer-ga on 23 Dec 2002 10:08 PST

To use the form control value as a parameter for a crosstab query, you
have to explicitly add it using the Parameters dialog box.
Tools/Parameters. If your value is at Forms!MyForm!EmplID then you
also have to put Forms!MyForm!EmplID in as a Parameter in the dialog
box.

Parameter queries:
1. Read the Help files
2. Read through the FAQ.

Some Links:
http://www.fontstuff.com/access/acctut01.htm
http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials/param.pdf

Search Terms:
Access "parameter queries"

---

Clarification of Answer by hammer-ga on 24 Dec 2002 04:46 PST

I will contact Google Answers and see if my Researcher contract
permits this. Because of the holiday, they may take a few days to
respond. I will let you know what they say. Either way, thank you for
the offer.

- Hammer

Was very quick to answer and had all the right information. Hammer
explained very cleary what I needed to know. He also gave me several
other options to try as well as links to more information. This
researcher is tops!