Query to run based on the day of the week

[gmazza76]

Good afternoon,
I have a query that always goes back to the database and re aligns data, but I think I am doing this the long way round.
Ideally if the query is run on a daily basis and I would like (if possible) to work out what day of the week it is and run accordingly as follows.
If Monday run the query from the previous friday and if its Tuesday - Friday just run the previous days

Currently I have the formula in my criteria in the Query as follows

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

Which only runs the data for Friday and not Saturday or Sunday.
I have tried using > but I don't think this is the correct procedure.

thanks in advance
Gavin

 

[James006]

Hi,

You can test: MsgBox IIf(Weekday(Date) = 2, Date - 3, Date - 1)

 

[Micron]

That will only use either Friday or the prior day, not from Friday to Saturday? My interpretation is that what is wanted is from Friday to Sunday inclusive when run on Monday. I think you need to include your date field name in your expression, so maybe like

[FieldName] >= IIf(Weekday(Date()=2),[FieldName]>Date()-3,[FieldName]=Date()-1)

which would include Monday if there is any data for it.

 

[gmazza76]

thanks for the above @Micron,

I tried the following and it pulled all the days through and not just yesterdays data as I was thinking it would.
Is it something easy I am missing?

[Call Date]>=IIf(Weekday(Date()=2),[Call Date]>Date()-3,[Call Date]=Date()-1)

thanks

 

[Micron]

It's not clear to me what you want. Maybe you could post data that makes that clear?
EDIT - make sure it shows for both Monday and not Monday situations.

 

[gmazza76]

Good afternoon,

I have moved a step closer with the above query as in the formula in the query, which will pull only the data from 3 days ago.
Does anyone now how I could do this to be from 3 days to present?

I have tried "between Date()-3 and Date()-1" or just >date()-3, but these bring back no data at all

IIf(DatePart("d",Int(Now()),2),Date()-3,Date()-1)

thanks in advance
Gavin

 

[Micron]

You have no operators in that. Where's your = or >= or <= ? Where's the "thing" you are comparing the values to ) [Call Date] )?
Test your expressions - put ?DatePart("d",Int(Now()),2) in the immediate window and press enter at the end of the line and what do you get? Ans. today, 31. The only day you will get 2 using that is on the second day of any month, regardless of what day that is. You're complicating things by using unnecessary functions (and probably ones that you don't understand completely). I think you were closer before.