IF Statement with multiple dates in select query

[thenoisydrum]

Hello,
I have found threads on similar issues but still cannot find a solution to my problem so here goes;

I have a query called Low Margin Transactions, that I guess speaks for itself.

I have a query based on this which selects the previous day's figures where the margin is below 8%. However on a Monday I would like the query to select Friday's, Saturday's and Sunday's low margin transactions. The query will run by a user so I don't want them to have to edit the query. I want it to be a simple click of a button for them.

As a starting point for the 3 previous days scenario, this actually works;
[Low Margin Transactions].[Day(D&T)]=Date()-1 Or [Low Margin Transactions].[Day(D&T)]=Date()-2 Or [Low Margin Transactions].[Day(D&T)]=Date()-3

If however I incorporate it into an IF statement it does not work;
IIf(Weekday(Date())=5, [Low Margin Transactions].[Day(D&T)]=Date()-1 Or [Low Margin Transactions].[Day(D&T)]=Date()-2 Or [Low Margin Transactions].[Day(D&T)]=Date()-3, Date()-1

I have the weekday = to 5 at the moment because today is Thursday, once I know the statement works it would obviously change to 1 for Monday.

Where am I going wrong with this? Both the true and the false statements work on their own.

 

[pr2-eugin]

The IIF statement will work as you want it to, i.e. IIF(condition, true_Statemet, false_Statement) you just need a closing bracket at the end..

IIf(Weekday(Date())=1, [Low Margin Transactions].[Day(D&T)]=Date()-1  Or [Low Margin Transactions].[Day(D&T)]=Date()-2 Or [Low Margin  Transactions].[Day(D&T)]=Date()-3, Date()-1)

However I do not understand the Logical OR in the statement. You said if it is Monday, you want the margin value of the past three days i.e. Friday, Saturday and Sunday.. So I am not sure why you have an OR.. should it not be an arithmetic '+' followed by a '/3'..
Which will give the Average margin of the three days?

 

[thenoisydrum]

Thanks pr2,
I copied your code into the query, changing the day to 5 and it didn't return any records......?
The reason that I don't group the 3 days is because I list the sales transactions down to invoice number and date detail therefore I'm happy, or rather need to keep all 3 days separate.

 

[pr2-eugin]

Okay, if you want only the Fridays Margin value, then try this, see if you get any result..

IIf(Weekday(Date())=5,[Low Margin Transactions].[Day(D&T)]=Date()-3, Date()-1)

In the above case you should get the value for Monday..

 

[thenoisydrum]

pr2,
Still no result......
The query returns yesterday's data where the statement is false but not when it is true.

Do you have any more ideas? It doesn't like it when the weekday is actually today (5 being Thursday)

 

[pr2-eugin]

Okay am sorry the above code will not work, looking at the code again.. Could you tell me what is actually inside [Low Margin Transactions].[Day(D&T)] ??

 

[thenoisydrum]

Day(D&T) is 25/07/2012 (for example) in Date&Time format.

If I change the code to the Following (removing the ref to the table and field) it actually works;
IIf(Weekday(Date())=5,Date()-3,Date()-1)

In this case it returns data from Monday - which is correct.
It falls over again though when I change the Date()-3 part to get my 3 previous days.
So this;
IIf(Weekday(Date())=5,Date()-1 Or Date()-2 Or Date()-3,Date()-1)
Does not return any data.
I've tried putting brackets around the true section and it makes no difference.....

 

[pr2-eugin]

Okay again.. You cannot use Logical OR here.. Could you give one example of what you are trying to do? If it is Monday... When you say you want to get the three previous data?? so where do you intend to place them?

 

[thenoisydrum]

Ok,
To do this manually on a Monday I could type into the query criteria the following;

Date()-1 OR Date()-2 OR Date()-3

This would fetch the low margin sales data for the Sunday, Saturday and Friday.

For Tuesday to Friday I could simply type in Date()-1.

To explain, we do process sales on a Saturday and a Sunday but most of the staff (including me) only work Monday to Friday.

 

[pr2-eugin]

right.. Now I get the Idea.. So under criteria you do this.. Date()-1 OR Date()-2 OR Date()-3.. I thought that it was a field you were trying to create... okay.. i think it should be simple.. could you post the SQL query?? please wrap them in

 tags..

 

[thenoisydrum]

Voila

SELECT [Low Margin Transactions].Day1, [Low Margin Transactions].PrevDay, [Low Margin Transactions].[Day(D&T)] AS [Invoice Date], [Low Margin Transactions].[Invoice Number], [Low Margin Transactions].Custcode, [Low Margin Transactions].Name, [Low Margin Transactions].BDM, [Low Margin Transactions].Salesman, [Low Margin Transactions].Fitter, [Low Margin Transactions].Group, [Low Margin Transactions].Status, [Low Margin Transactions].Manufacturer, [Low Margin Transactions].Description, [Low Margin Transactions].Qty, [Low Margin Transactions].Turnover, [Low Margin Transactions].Profit, [Low Margin Transactions].Cost, Round(([Profit]/[Turnover])*100,2) AS Margin
FROM [Low Margin Transactions]
WHERE ((([Low Margin Transactions].[Day(D&T)])=IIf(Weekday(Date())=5,([Low Margin Transactions].[Day(D&T)])=Date()-1 Or ([Low Margin Transactions].[Day(D&T)])=Date()-2 Or ([Low Margin Transactions].[Day(D&T)])=Date()-3,Date()-1)) AND ((Round(([Profit]/[Turnover])*100,2))<=8))
ORDER BY Round(([Profit]/[Turnover])*100,2) DESC;

 

[pr2-eugin]

Okay, instead of adding the criteria as

IIf(Weekday(Date())=5,([Low Margin  Transactions].[Day(D&T)])=Date()-1 Or ([Low Margin  Transactions].[Day(D&T)])=Date()-2 Or ([Low Margin  Transactions].[Day(D&T)])=Date()-3,Date()-1)

Try adding it as.. Criteria in each line..

IIf(Weekday(Date())=5,Date()-1,Date()-1)
IIf(Weekday(Date())=5,Date()-2,Date()-1)
IIf(Weekday(Date())=5,Date()-3,Date()-1)

so your SQL query will look something like.. WHERE (cond1) OR (cond2) OR (cond3)
Check the attachment...

 

[thenoisydrum]

That's it - you've done it!

Thanks pr2

 

[pr2-eugin]

Glad it worked.. Good luck moving forward..