IIF statement to execute select query when true and false

[wish24bone]

I was wondering if it was possible to write an IIF query, that depending on the day of the week it would execute specific select queries that I could then return in a subreport form.

something like:

IIF(WEEKDAY(DATE,2),<ON TRUE RUN SPECIFIC SELECT>, <ON FALSE RUN A DIFFERENT QUERY>);

Then be able to display this result on a form using a subreport.. Does this make sense?
Thank you in advance

 

[spikepl]

It smells of denormalised data. You can do what you want, but denormalised data will bite you and keep biting.

Read about normalisation.

If you show a pic of your Relations window, then someone can help you with that.

 

[wish24bone]

It's 1 table, the 2 select statements I want to run based on what day it is, will pull look back a certain amount of days..

IE.

If today is Monday then the true select would have a select that has a where condition that looks back 3 days to report data.. if today is not monday, then the false select will have a where condition that only look back 1 day to report data.

 

[MSAccessRookie]

It's 1 table, the 2 select statements I want to run based on what day it is, will pull look back a certain amount of days..

IE.

If today is Monday then the true select would have a select that has a where condition that looks back 3 days to report data.. if today is not monday, then the false select will have a where condition that only look back 1 day to report data.

In that case, you probably do not need two queries, and instead you can modify your WHERE statement so that it does what you want it to. The following {untested} code below might get you pointed in the right direction.

Select Stuff, MoreStuff, DateofStuff
FROM YourTable 
WHERE  DateofStuff > (Date() - IIf([B]{Date is Monday}[/B], 3, 1))

Notice that I did not complete the task. There are a number of ways to determine that the {Date is Monday}, and you can pick the one that suits you best.

 

[wish24bone]

Ok, I think I see what you mean.. I now get a malformed query error.. I think its how I'm determining the day of week.. Here is my query


SELECT Count([EDI Request Form].ID) AS Expr1
FROM [EDI Request Form]
WHERE ((([EDI Request Form].Modified) >(date()- IIf((WEEKDAY(DATE(),2), 3, 1)))
AND (([EDI Request Form].Status)='Closed'));

 

[vbaInet]

Can you spot the wrong bit:

WHERE ((([EDI Request Form].Modified) >(date()- IIf((WEEKDAY(DATE()[COLOR=Red],2[/COLOR]), 3, 1)))

 

[wish24bone]

Ok, I had other issues related parens. I fixed that and can get the query to run but it's not working right, it's pulling back the past 3 days and today isn't monday..

In your reply you refer to the ,2 as the problem.. but don't I need to have that to determine if the current date is monday? 2 is a Monday right?

Here is the corrected query, that executes but not pulling back the right data.

remember if today is monday then I want to look back 3 days, if not monday then only look back the previous day.

SELECT Count([EDI Request Form].ID) AS Expr1
FROM [EDI Request Form]
WHERE ((([EDI Request Form].Modified) > (date()- IIf((WEEKDAY(DATE(),2)), 3, 1))))
AND (([EDI Request Form].Status)='Closed');

 

[vbaInet]

Yes but how do you test equality? With a comma (,) or with an equal to (=) sign?

 

[MSAccessRookie]

Ok, I had other issues related parens. I fixed that and can get the query to run but it's not working right, it's pulling back the past 3 days and today isn't monday..

In your reply you refer to the ,2 as the problem.. but don't I need to have that to determine if the current date is monday? 2 is a Monday right?

Here is the corrected query, that executes but not pulling back the right data.

remember if today is monday then I want to look back 3 days, if not monday then only look back the previous day.

SELECT Count([EDI Request Form].ID) AS Expr1
FROM [EDI Request Form]
WHERE ((([EDI Request Form].Modified) > (date()- IIf((WEEKDAY(DATE())=2), 3, 1))))
AND (([EDI Request Form].Status)='Closed');

Your Syntax for the WEEKDAY Function was incorrect. It uses the second parameter of the WEEKDAY Function to set the first day of the week to Monday. It also does not check the value returned by the WEEKDAY Function, and therefore returns TRUE for all days. The default week is (Sunday=1 ... Saturday=7), so Monday is day 2, and today (Thursday) would be day 5. You need to compare the current weekday ID to a value to do what you want. Try the modification that is displayed above, and see if it works for you.

 

[wish24bone]

Thats it!!!! Thank you very much for your help..

 

[MSAccessRookie]

Always glad to help assist a fellow traveler