"And If"

[jd_boss_hogg]

I know that 'And If' isn't good syntax, but can't work out which way to lay this query out.

It's a simple query that reports back stuff from yesterday. However, on a Monday this isn;t relevant because i actually need Friday data. So, somehwere in this code i need .... if (weekday(date) = 2, Date()-3, Date()-1)

SELECT [jb-2001].CUST_REF, Format([OUT_DATE],"dd/mm/yyyy") AS Expr1, *

FROM [jb-2001]

WHERE ((([jb-2001].GONE)="YES") AND ((Format([OUT_DATE],"dd/mm/yyyy"))=Date()-1))

ORDER BY [jb-2001].[Job No];

Can anyone be kind enough to help me out with this ? I feel i need to code it as .... AND IIf (Weekday(date) = 2, ((Format([OUT_DATE],"dd/mm/yyyy"))=Date()-3)), ((Format([OUT_DATE],"dd/mm/yyyy"))=Date()-1)))

but i know i can;t do an "AND IIf...."

 

[DCrake]

If you create a query based on the source table and add a new column

RptDate:Iff(WeekDay(Date()) = 1 ,Date()-3,Date()-1))

Then in a second column

Wanted:Iff(RptDate=Out_Date,True,False)

Then under the wanted column enter True in the condition row.

This way you should only see data for either the previous day or Friday in the case of date being a Monday.

This is all aircode and as such untested.

David

 

[namliam]

Can anyone be kind enough to help me out with this ? I feel i need to code it as .... AND IIf (Weekday(date) = 2, ((Format([OUT_DATE],"dd/mm/yyyy"))=Date()-3)), ((Format([OUT_DATE],"dd/mm/yyyy"))=Date()-1)))

but i know i can;t do an "AND IIf...."

Why the complexity? Why not KISS, what is the part that changes?? -1 to -3... so change that... only.... something like so:

AND [OUT_DATE] = Date() - IIf (Weekday(date) = 2, -3, -1)

I would like to add, that doing this
Format([OUT_DATE],"dd/mm/yyyy")=Date()-1

Formatting a date to a Text then comparing it with a date, is BAD, this causes implicit conversions which have a tendancy to cause "unexpected results"

Avoid implicit conversions like this like the PLAGUE!!!

 

[raskew]

Hi -

Assuming non-workdays are Saturday (7) and Sunday (1), the following returns previous workday. I've used X to represent a date field. Replace
this with your date field:

Examples:
Tuesday, 10/27/2009
x = date()
? x - IIf(WeekDay(x) < 3, 1 + WeekDay(x), 1)
10/26/2009

Monday, 10/26/2009
x = date() -1
? x - IIf(WeekDay(x) < 3, 1 + WeekDay(x), 1)
10/23/2009

Sunday, 10/25/2009
x = date() -2
? x - IIf(WeekDay(x) < 3, 1 + WeekDay(x), 1)
10/23/2009

Saturday, 10/24/2009
x = date() -3
? x - IIf(WeekDay(x) < 3, 1 + WeekDay(x), 1)
10/23/2009

HTH - Bob