Date Query again...

[Angie K.]

I posted yesterday the message below about a function to
find out the current workweek for a deadline list. The
answer below helps me find out the weekday, which i
suppose i can write into a huge formula which figures out
If(now)=2, then IIf([dtDateDue] Between (Now()-1) And (Now
()+5),1,, or something like that. This is going to get
huge and messy. Is there a function to just find the
current week no matter what the day is?
Thank you,
Angie

response:
Use the Weekday function to determine what day of the week
it is.
Weekday(Date) will return 2 for Monday, 3 for Tuesday, 4
for Wednesday and
so on. Factor that into your calculation (sorry: too lazy
to do the math
myself right now!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Good Afternoon,
I created a deadline list (an access report)for our
department based on a query. The information, based on the
week it is due, is sorted by week. So, i created a field
in my base query:

Week: IIf([dtDateDue] Between (Now()-1) And (Now() +5),1,IIf
([dtDateDue] Between (Now()+6) And (Now()+12),2,IIf
([dtDateDue] Between (Now()+13) And (Now()+19),3,IIf
([dtDateDue] Between (Now()+20) And (Now()+26),4,IIf
([dtDateDue] Between (Now()+27) And (Now()+33),5,IIf
([dtDateDue] Between (Now()+34) And (Now()+40),6,IIf
([dtDateDue] Between (Now()+41) And (Now()+47),7,IIf
([dtDateDue] Between (Now()+48) And (Now() +54),8,0))))))))

this catalogues two months naming the weeks--week 1, week
2, etc. This is a fabulous way for us to look at our
deadlines. i have color coded the report to change every
other week. The problem is is that this is a great formula
if it is monday, but no other time of the week. At times,
we have to update the deadline list throughout the week
and it changes the week. Is there a way to create this
formula to always query for the current week monday
through friday (deadlines never happen on weekends)?
Any ideas would be awesome.
Thanks,
Angie

..

 

[John Vinson]

Is there a function to just find the
current week no matter what the day is?

DatePart("ww", [datefield])

will return the week number within the year (an integer 1 to 54).

Or, you could use a criterion

BETWEEN DateAdd("d", 1-Weekday(Date()), Date()) AND DateAdd("d",
7-Weekday(Date()), Date())

to retrieve all dates during the current week.

John W. Vinson[MVP]
(no longer chatting for now)