Query to run based on the day of the week

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
725
Office Version
  1. 365
Platform
  1. Windows
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
Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,756
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,613
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:
Upvote 0

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
725
Office Version
  1. 365
Platform
  1. Windows
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?

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

thanks
 
Upvote 0

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
4,613
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

Forum statistics

Threads
1,186,326
Messages
5,957,214
Members
438,293
Latest member
ginkycart

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top