Is there an easier way to do week range?

[Ares6881]

Hi, I was wondering if there's an easier way to get a week range than what I'm currently using. Right now I use something along these lines, where drequest is the date

Format([drequest],"yyyy") & IIf(Format([drequest],"ww")<10,"0" & Format([drequest],"ww"),Format([drequest],"ww"))

this would be an example criteria for it to find out all dates between weeks 7 and 35

between 200607 and 200635

The biggest problem I have with this is if I just do
Format([drequest],"yyyyww")
any week before the 10th week wont put a 0 in front of it and then you end up with numbers like 20067.

Anyway, just wondering if there's an easier way.

[Rabbit]

Seeing as how you already have the date, why do you need to format it into a string?

[Ares6881]

Seeing as how you already have the date, why do you need to format it into a string?

Because we go by ship weeks here and weeks vary depending on the year, I've created a report that goes off of week instead of date to help the users enter data in a format they understand instead of having to look up what the beginning and end of each week is every time.

An example is august 6th this year falls on the week from august 5th to the 11th, next year it will fall on the week of august 3rd to the 9th, the year after that will be the 2nd to the 8th, and so on.

[Rabbit]

drequest is a date though right? Where does that come from if they enter date in a text format?

This will give you the same results.

Year(drequest) & Right("0" & Month(drequest), 2)
 

[Ares6881]

drequest is a date though right? Where does that come from if they enter date in a text format?

This will give you the same results.

Year(drequest) & Right("0" & Month(drequest), 2)
 

that wont give the same thing, I'm using "ww" for weeks, not "mm" for months, however, I can substitute the week in there and that should work, thanks ;)

[Rabbit]

Yeah, week().. that's what I meant.

Good luck.