query for last 7 days - mon to mon

[louis-m]

trying to think my way around this one. i am in search of a query that will allow users to see what they have ordered within the last week (7days max) which starts on monday of each week eg if they run the query on thurs, they will see mon, tues, weds & thurs and not just 7 days from thurs [Date()-7]

i'm assuminging its gonna be something like if day=monday then query will = date() else if day=tuesday then query will = date()-1 and so on.....

 

[pbaldy]

I think you'll find what you need here:

http://www.pacificdb.com.au/MVP/Code/Dates.htm

 

[Rabbie]

Remember you can use the weekday function to test which day it is.

Your search criteria would be something like "Between Date() and Date()-Weekday(date(),2)+1"

 

[louis-m]

lol.... my heads spinning now. gonna have to look more into the date function i think. all i require is users to be able to see orders back to the begining of work week ie monday

 

[Brianwarnock]

This Thread will give you the idea.

Brian

 

[louis-m]

would >=WeekdayName(2,FALSE,vbMonday) AND date()-7 work? i see the above as returning values within the last 7 days with a weekday greater than monday.

 

[Rabbie]

Louis, What I put in my previous post works for me a a similar situation

 

[louis-m]

hi rabbie,
thanks for taking time on this..... i'm trying to figure out how your formula works.

"Between Date() and Date()-Weekday(date(),2)+1"

and it works like a treat........ but how?

 

[Brianwarnock]

As you will have read in the help on Weekday you can shift the start of the week from sunday when calculating the day number thus Rabbie has shifted it to Monday. If we consider today this means that Tuesday is now day 2 and to get Mondays date we need to subtract the day number (2) and add 1 as Monday is 1, then its a simple Between.... And construct, although I admit that I would have written it the other way round.

BTW you haven't got times in your order date/time field have you?

Brian

 

[Rabbie]

hi rabbie,
thanks for taking time on this..... i'm trying to figure out how your formula works.

"Between Date() and Date()-Weekday(date(),2)+1"

Date() gives today's date and Date()(date(),2) +1 gives the date of the most recent monday so the whole expression will select dates between monday and today.

 

[Rabbie]

As you will have read in the help on Weekday you can shift the start of the week from sunday when calculating the day number thus Rabbie has shifted it to Monday. If we consider today this means that Tuesday is now day 2 and to get Mondays date we need to subtract the day number (2) and add 1 as Monday is 1, then its a simple Between.... And construct, although I admit that I would have written it the other way round.

BTW you haven't got times in your order date/time field have you?

Brian

Thanks Brian for the explanation. Much better than my attempt

 

[louis-m]

nah.... the users are told they have to complete the weeks orders before 10pm on sunday night but that can mean last thing on sunday night to keep things simple.
the main office works mon -fri and places the orders on monday only. the remote sites only need to see what they have ordered mon - sun each week for the last week and then have their forms/reports restart from monday which then denies the records being changed once monday has passed.
the main office obviously has to run reports on last week, last month, quarter and yearly.

i have the date being put in automatically and locked although the users need the ability to delete or edit the records items/amounts but not edit the date the record was added.

the above formual seems to fit the bill perfectly and has give me more than enough to be getting on with. thanks for the help and taking time to explain. greatly appreciated. louis

 

[Rabbie]

Happy to have been of help

 

[louis-m]

Hi,
had to ressurect this thread and many thanks to the people who helped.
the above formula works perfectly for monday to sunday.
does anybody know how you would alter it so you could have another query which showed the previous weeks results eg working week from two weeks ago etc

 

[namliam]

Just take away 7 days from this
"Between Date() -7 and Date()-Weekday(date(),2)+1 -7"

Offcourse this is not exactly what you are looking for ... but still you get what I mean (I hope)

 

[gemma-the-husky]

the easiest way to do what you want is to put a date field on your form - say called testdate

if you want to only use mondays eg, then you can just say

if weeekday(testdate)<> vbmonday then
msgbox("Invalid Date - please pick a monday")
end if

so for any particular data range, based on the date just selected then in your query, just put as a criteria

between testdate and testdate-7

-------
or you could have two date fields testdate1 and testdate2 then just put

between testdate1 and testdate2

-------
now the only thing is, is you cant use a field/variable just like that in a query - you have to use the correct field reference in the query to read the date - so you get a change to the syntax with

between Forms![myform]![testdate1] and Forms![myform]![testdate2]

----------

none of this is hard - its standard access query stuff, and after you do it once it will be plain sailing
--------

as an alternative, to entering a date into a field, you can use a date picker (examples on this forum) ie a pop up calendar and pick a date from that.

 

[louis-m]

hi gemma,
thank you very much for that and i was going to use something similar to that for dates further back.
the users only require to really see back a few weeks without going into too much clicking here and there (its hard dragging them away from single excel spreadsheets!)
so i have a tab control 3 subforms with:

this weeks orders:
"Between Date() and Date()-Weekday(date(),2)+1"

last weeks orders:
"Between Date()-7 and Date()-Weekday(date(),2)-6"

previous weeks orders:
"Between Date()-14 and Date()-Weekday(date(),2)-13"

 

[Atomic Shrimp]

hi gemma,
thank you very much for that and i was going to use something similar to that for dates further back.
the users only require to really see back a few weeks without going into too much clicking here and there (its hard dragging them away from single excel spreadsheets!)
so i have a tab control 3 subforms with:

this weeks orders:
"Between Date() and Date()-Weekday(date(),2)+1"

last weeks orders:
"Between Date()-7 and Date()-Weekday(date(),2)-6"

previous weeks orders:
"Between Date()-14 and Date()-Weekday(date(),2)-13"

Trouble is with hard-coding menu options like that is, sooner or later, someone will want to know about the orders from the week before the week before last.

Gemma's solution of using a date picker to select one end of the date range will mean that when this happens, you don't have to re-code the application.

 

[gemma-the-husky]

this weeks orders:
"Between Date() and Date()-Weekday(date(),2)+1"

last weeks orders:
"Between Date()-7 and Date()-Weekday(date(),2)-6"

previous weeks orders:
"Between Date()-14 and Date()-Weekday(date(),2)-13"

incidentally you dont need all this fancy stuff with weekday etc

just
Between Date() and Date()-6
Between Date()-7 and Date()-1
Between Date()-14 and Date()-20

will work fine

 

[louis-m]

Hi Gemma,
the reason the weekday is in there is because users only need to edit orders in the current working week and not the last 7 days eg if they looked at the orders on wednesday, they can only edit back to the monday that week and not the last 7 days.
the current working week orders are editable whereas afterwards, they can only view. we need this so we can stop remote users from editing the previous working weeks records which then throw the monthly reports out.
they have realised that the office only looks at the current week and monthly reports. consequently, some users have been altering records from 3 & 4 weeks previous to bring the monthly target report into line knowing that the office doesn't look back if targets are met.
so i need working weeks that range from monday to monday (which the above formula does), wednesday to tuesday & friday to thursday depending on the remote site that places the order.