Automate a Harder Date Field

[simon4amiee]

Hi again this is slightly more complex, again I run a report based on dates I enter (see below)

>=[Date you wish to view DD/MM/YY] And <=[Enter the Date after DD/MM/YY]

I would like to automate this if possible, the dates are enter are always a set pattern

If its Monday I enter the previous Thursday and Sunday
If Its Tuesday I enter the previous Sunday and Monday
If Its Wednesday I enter the previous Monday and Tuesday
If Its Thursday I enter the previous Tuesday and Wednesday
If Its Friday I enter the previous Wednesday and Thursday
I dont run rthe report on weekends

 

[namliam]

>=[Date you wish to view DD/MM/YY] And <=[Enter the Date after DD/MM/YY]

Stopped reading here, dates need to be entered in MM/DD/YYYY format to ensure things work properly always

I would like to automate this if possible, the dates are enter are always a set pattern

So the last two "work days"???
Though I dont understand Thursay and Sunday on monday, shouldnt that be Thursday and Friday?

Anyhow, something along these lines should work for you
>=Date() - Iif(Weekday(date(),2)<3,4,2) and <Date()

<Date is the "same thing" as <=Date()-1, but its a "little better"

 

[simon4amiee]

Stopped reading here, dates need to be entered in MM/DD/YYYY format to ensure things work properly always



So the last two "work days"???
Though I dont understand Thursay and Sunday on monday, shouldnt that be Thursday and Friday?

Anyhow, something along these lines should work for you
>=Date() - Iif(Weekday(date(),2)<3,4,2) and <Date()

<Date is the "same thing" as <=Date()-1, but its a "little better"

Huge thanks for that my friend, however working in a hospital although I dont work weekends the report needs to tak into account the weekends, thus on a Monday I run an extra 2 days worth before going back to the usual routine during the week

 

[namliam]

Which is what the statement does? Did you try it?

 

[simon4amiee]

Do I have to put my field name anywhere in the criteria of just copy the above, I have run it and ended up with more records than the one i ran today using my old one.

 

[namliam]

Without sample data, no way to be sure, you should be able to use the "code" given in the criteria of your query design.

One difference can be in the DD/MM/YYYY format vs MM/DD/YYYY, which may well cause an error on your side of things... i.e. 12/03/2014 will "transform" into the DATE of DEC/12/2014, not 12/MAR/2014. Access requires/expects/defaults MM/DD/YYYY.

My <Date() instead of your <=03/11/2014 may cause an issue if your date/time field also has time. like 03/11/2014 16:00
Your <=03/11/2014 will not pick 4 pm up, where the <Date() I gave you will pick that up.
Not sure what your requirements exactly are or where it may be going wrong but above should give you a good idea.

If you cant figure it, try posting some sample data preferably already in a database and I can tell you whats going wrong where and why in detail.

 

[simon4amiee]

Ah got it mine was picking up 1 date [AttendDate] and your has picked up 2 dates, to simplify I was running a report for all attendances on a single day except when I run it on a monday as this will be Fri, Sat & Sun (s days). I probably got it wrong somewhere.

When I took out the additional days data from yours my figures matched.

 

[simon4amiee]

>=Date()-IIf(Weekday(Date(),2)<3,4,2) And <=Date()-1

This did the trick and matches exactly to my orginal report, Ill trial this for a week running both to ensure it works, huge thanks you my friend and very much appriciated.