If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Query
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 |
#2
|
|||
|
|||
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." wrote in message ... 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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
Access Query to request record with date closest to todays date | Pete Sperling | Running & Setting Up Queries | 4 | July 30th, 2004 03:51 PM |
Query Criteria where a single date comes from a form | Gary O | Running & Setting Up Queries | 3 | July 23rd, 2004 05:24 PM |
Parameter Query on date using MS Query | Brian | Links and Linking | 0 | January 27th, 2004 07:05 AM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |