Query Criteria

[Louise]

In my query I have a field titled "TurnaroundDays"
and "DateReceived". There are different values for
turnaroundDays for different Case Types. The normal
turnaround time is 2, however for
"CaseType10" and "CaseType20" I need the "TurnaroundDays"
to = 4 for these specific "DateReceived" 01/08/04 and
01/09/04 and 01/15/2004 and 01/16/2004.

I want to use and expression in my "TurnAroundDays" query
criteria. I've tried

If [DateReceived]= 01/08/2004 and 01/09/2004 and
01/15/2004 and 01/16/2003 for [tblCases Casetype
CaseTypeID=10 and CaseTypeID=20]then[TurnaroundDays] = 4

I'm new to access and there is something wroing with the
format of my code. Any help you can give me is
appriciated or am I going about this the wrong way?

Thank you

 

[Tom Ellison]

Dear Louise:

In the first part:

If [DateReceived] = 01/08/2004 and 01/09/2004 and 01/15/2004 and
01/16/2003

I think you mean to limit the DateReceived to those 4 dates. That can
be expressed as:

WHERE DateReceived = #01/08/2004# OR DateReceived = #01/09/2004#
OR DateReceived = #01/15/2004# OR DateReceived

Some things to note about this:

- I don't know where you got the idea for If being used here, but it
doesn't fit any context I can see.

- The DateReceived cannot be 4 different values, but it can be one of
4 different values. This is OR instead of AND.

- Date literals (constant date values) must be enclosed in ## as
shown. Otherwise, 01/08/2004 is interpreted as 1 divided by 8 divided
by 2004.

A shorter, less confusing statement would be:

WHERE DateReceived IN(#01/08/2004#, #01/09/2004#, #01/15/2004#,
#01/16/2004#)

The rest of what you are saying is hard for me to imagine what you
mean, things like "for" and "Casetype". Please try to explain the
logic in English.

The above assumes you're using Jet and not MSDE or SQL Server as your
engine. For MSDE or SQL Server, put the dates in single quotes
instead of ##.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts