date query criteria for monday and friday of current week

[UNDPC]

Hi,
I would like to dynamically set the criteria of a field(s) in a query to be the monday and friday of the current week. Does anyone have any ideas on how to do this? Basically I am tracking records based on the timestamp on a week to week basis but do not want to have to update the criteria each week. I hope this helps explain what I am looking for. Thanks.

 

[TonyJollans]

Now I follow

I was referring to UNDPC's post a couple before yours, where he was referring to his Excel formula.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[BobJacksonNCI]

UNDPC,

You can see how important it is to word questions carefully...

I believe you want to include all entries dated between Monday and Friday of the current week. Further, the field you want to use for comparison is a timestamp - meaning it includes the date and time. If my understanding is correct, you can select the information by using the following as the criteria for the [DEL_TIMESTAMP] field:

Datevalue([DEL_TIMESTAMP]) Between (Date() - WeekDay(Date())+2) and (Date() - WeekDay(Date()+6))

HTH,
Bob

 

[UNDPC]

Tony,
That works perfectly. Thanks for your help.

Skip,
Thanks for all the time put in trying to help me figure this out.

This helps me soooo much. Thanks guys.

 

[UNDPC]

Tony,
Along these same lines, how could I set the query criteria to be the First of the Month dynamically?
I have several queries where the date criteria is the 1st of the month and I have to change it each month. I would like to have the criteria be dynamic so I do not have to remember to change it all the time.

 

[TonyJollans]

Hi UNDPC,

Try using ..

[tt][blue]DateSerial(Year(Date()), Month(Date()), 1)[/blue][/tt]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[UNDPC]

Tony,
I really appreciate all your help here. One more little tidbit, since I'm not up on all the syntax for this...

What would the same line be to display the 1st day of the Previous Month for the criteria?

 

[TonyJollans]

Hi UNDPC,

Sorry, was a bit brief before. The functions involved are ..

[blue][tt]Date()[/tt][/blue], of course, and ..

[blue][tt]Year(SomeDate)[/tt][/blue], and
[blue][tt]Month(SomeDate)[/tt][/blue], and
[blue][tt]Day(SomeDate)[/tt][/blue] ..
which return the individual elements of SomeDate, and ..

[blue][tt]Dateserial(SomeYear, SomeMonth, SomeDay)[/tt][/blue] ..
which builds a date from its constituent parts and, the nice part, makes 'corrections' so that, for example, a Day of zero gives the last day of the previous month.

What I gave you before took the year of today, the month of today, and Day 1 to return the first of this month.

To return the first of last month, use ..

[blue][tt]DateSerial(Year(Date()), Month(Date()) - 1, 1)[/tt][/blue]

.. and if it's January it will fall back to December of the previous year.

To get the last day of this month, use ..

[blue][tt]DateSerial(Year(Date()), Month(Date()) + 1, 0)[/tt][/blue]

Hopefully, you get the picture from those couple of examples and can build your own. If not, please do come back.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[UNDPC]

Thanks a bunch Tony.

 

[UNDPC]

Tony,
I have one more problem with this date criteria and was wondering if you could help a little more.

The date range has changed for this process and I cannot get the criteria correct after many tries. Now we want to have the criteria be between:

The Tuesday of the the current week and
the Monday of next week.

However, when I try and do the date criteria, when it gets to Monday of next week it switches the Tuesday of the current criteria to be the Tuesday of next week and then the following Monday. I basically don't want this to happen until Tuesday, though. Do you understand what I'm getting at? I think it has something to do with the WeekDay and Date() function but I'm not sure. Here is what I have currently:

Between Date()-Weekday(Date(),2)+2 And Date()-Weekday(Date(),2)+8

 

[BobJacksonNCI]

Hi UNDPC!

I'm not Tony (not as knowledgeable, but much handsomer LOL). The expression is based on today's date, so your result makes perfect logical sense.

Date(),2

2 Is stating the first day of your week is Monday. Try changing all ,2 to ,3 and see what happens.

I'd like to test it myself, but my networked PC is locked to the server date so I can't.



HTH,
Bob

 

[TonyJollans]

Hi UNDPC,

I agree with Bob - about the code bit, anyway

[blue][tt]Between Date()-Weekday(Date(),3)+1 And Date()-Weekday(Date(),3)+7[/tt][/blue]

.. should do it

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.