Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Current Week (A97)

    Is there any way I can set a criteria in QBE to select any dates within the database that fall within the current week. For example, if I have a due date field that is to be returned in my query...I'd like to restrict it to only those records that are due within the current week.

    Thanks,

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Current Week (A97)

    Yes, it can be done. If you want to do it the simple way, ask for any records due within 7 days, as that math is pretty easy:
    The criteria shoul look like < Date() + 7

    If you really want to do it within the current week, it gets more complicated, as you need to use the Weekday() function to determine the numeric day of the week, and then subtract that number from the current date. It would look something like < Date() - Weekday(Date()) + 7
    Note that this can get complicated if you are running with international dates and the like - see the help (under VBA in 2000/XP) for Weekday() to see the gory details.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Current Week (A97)

    Wendell is right about using the Weekday function, but I don't agree with his selection criteria. You need to determine both the first and last day of the week. I'd use this:
    [yourDate] >= date() - Weekday(date() ) +1 AND [yourdate] < date( ) - Weekday( date( ) ) + 8

    This essentially gives you a range >= first day of this week and < the first day of next week. This will work even if [yourdate] has a time component.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Current Week (A97)

    Sorry - I was assuming that you might want to see anything where the data had already gone by as well. If the need to to only show things in the current week, and ignore any dates that are older, your solution is correct.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Current Week (A97)

    It's amazing the assumptions we make, often without thinking. Because I had just been working on a report that was a "show me all deliveries due next week" for a client, I immediately thought of showing only what was due in a particular week. You made the assumption of showing everything from the end of this week back, and I bet someone else would have suggested everything from the beginning of this week forward!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Current Week (A97)

    You've both been helpful...before, I was trying to use this formula: >=(Date()) AND <=(Date()) + 5
    to give me dates in the current "business week"...but i'm not sure that would have held up so well for wednesdays - fridays.
    I think it would have spilled over into the next week. At any rate, i have it now...

    Thanks again,

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Current Week (A97)

    It's context-sensitive, Mark! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •