Let me try to explain this again

  • Thread starter Thread starter Louise
  • Start date Start date
L

Louise

I'm trying to create a report to reflect productivity by
an established turnaround time. I need this report to
reflect the entire month. The normal turnaround time is
2. For a few dates of the month, I need the turnaround
time to be 4. In my query, my field
titled "TurnaroundDays" I'm trying to add a Where criteria
that would say for these specific dates use 4 instead of 2
for the turnaround time. Any ideas how I could enter the
criteria for all dates to use 2, but for a few specific
dates, use 4? This is all I've got so far,

WHERE DateReceived = (01/08/2004) OR DateReceived =
(01/09/2004)

Thank you,
 
Dear Louise:

This is a lot clearer. In your criteria, your dates still need ##
around them for Jet, and single quote for MSDE. Plus, the IN() syntax
is briefer and easier.

But your main question is to allow a user to select an indeterminate
number of dates for the query.

There is a good control available to do this - a list box allowing
multiple selections. You list a range of possible dates, and the user
clicks on several of them to make the desired selection.

You must then program to use these selections in the query.

One common alternative is to build the IN() clause as I showed in your
ealier post, iterating through the selected dates in the list box and
inserting them in the SQL text. This works well as long as the number
of selections is not too great, but the SQL could exceed limits and
fail if the number of selections is high.

You can also create a local, temporary table of the selected dates and
join on that to filter the query. Again, you must program an
iteration through the list box and insert each date in a row of the
table.

Perhaps this is more helpful now that we've narrowed the understanding
of what you need.

But, don't forget the ## or '' around dates, except with the joined
table solution above.

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