PC Review


Reply
 
 
Angie K.
Guest
Posts: n/a
 
      7th Sep 2004
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
 
Reply With Quote
 
 
 
 
Douglas J. Steele
Guest
Posts: n/a
 
      8th Sep 2004
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
http://I.Am/DougSteele
(no e-mails, please!)



"Angie K." <(E-Mail Removed)> wrote in message
news:799c01c49511$f9d64b90$(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
 
John Spencer (MVP)
Guest
Posts: n/a
 
      9th Sep 2004
You might take a look at the DatePart function or use the format function to
extract the week number.

DatePart("ww",Date()) returns 37 on my computer

Format(Date(),"ww",1,3) returns 36
since I specified that Sunday was the first day of the week (1) and I wanted to
start counting with the first full week of the year (3).

Be warned that the week number can get a little strange around the first of the
year/end of the year. In that for the last few days of December you might have
a week 53 in 2004 and a week 1 in 2005 for the first few days of January even
though you might consider all those days to be in the same week. Read the help closely.

"Angie K." wrote:
>
> 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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Link date to a table with Date Range (Eff Date and term date) =?Utf-8?B?S2V2aW4gUmVlZA==?= Microsoft Access 1 31st Aug 2006 04:23 PM
in a query test on date to see if it falls between two date date? =?Utf-8?B?ZGF0ZSBjYWxjIGluIHF1ZXJ5IGluIGFjY2VzcyAy Microsoft Access Queries 1 1st Jun 2006 04:34 AM
i have two date fileds Opend date Due date, can i set default on due date so, its always = to open date on my data entry form1 Urgent Mike Saifie Microsoft Access Form Coding 1 9th Mar 2006 02:08 AM
Run Query from date to date, Print the from date to date in the header of the report? Dustin Swartz Microsoft Access Queries 1 25th Jan 2005 08:06 PM
Query week to date, month to date, year to date hours =?Utf-8?B?VHk=?= Microsoft Access Queries 1 15th Dec 2004 04:46 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:19 AM.