1. ## 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. ## 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.

3. ## 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.

4. ## 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.

5. ## 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!

6. ## 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. ## Re: Current Week (A97)

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

#### Posting Permissions

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