Date Query

Discussion in 'Microsoft Access Queries' started by Angie K., Sep 7, 2004.

  1. Angie K.

    Angie K. Guest

    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
     
    Angie K., Sep 7, 2004
    #1
    1. Advertisements

  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
    news:799c01c49511$f9d64b90$...
    > 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
     
    Douglas J. Steele, Sep 8, 2004
    #2
    1. Advertisements

  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
     
    John Spencer (MVP), Sep 9, 2004
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. KL
    Replies:
    4
    Views:
    2,732
    John Spencer (MVP)
    Dec 16, 2003
  2. Guest

    Query week to date, month to date, year to date hours

    Guest, Dec 14, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    628
    Duane Hookom
    Dec 15, 2004
  3. Dustin Swartz
    Replies:
    1
    Views:
    700
    PC Datasheet
    Jan 25, 2005
  4. Guest
    Replies:
    1
    Views:
    392
    Arthur
    Jun 1, 2006
  5. Shoan

    Date Query using date in the date field

    Shoan, Aug 13, 2009, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    232
    John Spencer
    Aug 13, 2009
Loading...

Share This Page