Complex Criteria Using "IN()"

[Arjay418]

Hi all,

Here is a segement of an SQL statement that I'm using as the CRITERIA in a query.

The idea is to use "Date()-4" as the criteria in the query if the condition presented here is met.

This condition is described in words as: If today is Monday, AND (the date three days ago was 01/01/06 OR the date three days ago was 02/20/06 OR the date three days ago was 05/29/06, etc...) then the criteria used in the query will be the date four days ago.

IIf(Weekday(Date())="2" And (Date()-3="01/01/06" Or Date()-3="02/20/06" Or Date()-3="05/29/06" Or Date()-3="07/04/06" Or Date()-3="09/04/06" Or Date()-3="10/09/06" Or Date()-3="11/10/06" Or Date()-3="11/23/06" Or Date()-3="12/25/06"),Date()-4,.....

I would like to modify/simplify this statement using the IN function so that it might say: If today is Monday, AND (the date three days ago was IN (01/01/06, 02/20/06, 05/29/06, etc...) then the criteria used in the query will be the date four days ago.

What's more, I'd really like to be able to say: If today is Monday, AND (the date three days ago was IN(SELECT [HolidayDates] from [tblHolidayDates])) then the criteria used in the query will be the date four days ago. That way I can update the dates in one table (the tblHolidayDates table) instead of retyping them in this statement.

I'm struggling to get the "IN()" function to work in this context because I'm not sure of the syntax, or even whether it could work at all.

Any thoughts?

Thanks,
RJ

 

[SkipVought]

Hi,

For starters, dates are not strings (enclosed in QUOTES "")

Rather use #12/25/2006# as delimiters.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[Remou]

These dates appear to be holidays. Perhaps a small table and:
IN (Select HolDates From tblHols)

 

[Golom]

Try something of the form

Where WeekDay(Date) = 2 
  AND Date()-3 IN (Select [HolidayDates] from [tblHolidayDates])

This of course means that the query will return records ONLY on Monday when the preceding Friday was a holiday. Sounds like kinda limited usage.

If you want the hard-coded version its

Where WeekDay(Date) = 2 
  AND Date()-3 IN (#01/01/06#, #02/20/06#, #05/29/06#, etc...)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Arjay418]

Cool guys,
thanks. It works!

--RJ