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.

 

[SkipVought]

Hi,

in the criteria
[tt]
Format([mydate],"ddd")="Mon" or Format([mydate],"ddd")="Fri"
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UNDPC]

Skip,
I would like to, if possible, not have to use a parameter.
Also, it looks like this is just reformatting the date to display as Mon or Fri, which is not what I'm looking for. I would like the criteria to update itself based on the current date, Date(), and the parameters being the monday and the friday of the week. I know how to accomplish in cells in excel, but not in a query in access. Do you need more info? I really appreciate the help.

 

[SkipVought]

This criteria says that if the Field (in my case the field is mydate) is a Monday or Friday, then select the row.

Is that NOT what you want?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UNDPC]

No. First, the date is formatted like 4/1/2004 and not a day of the week. Second, I would like to select the rows that are between the current Monday and current Friday. So without it being dynamic, the static criteria would be:

>=#3/29/2004# And <=#4/2/2004# if it were this week

I want to replace the 2 dates with the dynamic "Monday" date and "Friday" date. Is this possible?

 

[SkipVought]

UNDPC,

A Date is JUST A NUMBER like today is 38078. Happens that you can FORMAT the date "m/d/yyyy" and it will DISPLAY 4/1/2004

OR

you can FORMAT the same date, "ddd" and it will DISPLAY "Thu"

OR

you can FORMAT the same date, "dddd" and it will DISPLAY "Thursday"

OR..............................................

NOW

what do you need to do with your DATES that can be FORMATTED with a 'Day of the week' in order to SPECIFY a CRITERIA???


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UNDPC]

Sorry Skip, I guess I'm just not grasping the concept here. I understand that you can format to appear as a day of the week. Maybe I should try and say it like:

[DEL_TIMESTAMP] is a timestamped field

In my query I would select all the rows where the value of [DEL_TIMESTAMP] falls in the current business week (Monday through Friday), but not have to update the criteria field each week with the current dates of the Monday and Friday.

 

[SkipVought]

Tell me WHERE the CURRENT DATE is in this Criteria?
[tt]
Format([DEL_TIMESTAMP],"ddd")="Mon" or Format([DEL_TIMESTAMP],"ddd")="Fri"
[/tt]
(insert the date-field of your choice...

???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Alternatively, it could also be written
[tt]
Format([DEL_TIMESTAMP],"ddd") In ("Mon","Fri")
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UNDPC]

I do not have the CURRENT DATE in the criteria anywhere yet, that is part of the question I am asking. Where in the syntax to put Date(), and then the rest to select only the records that fall in the current business week.

It seems that what you are telling me to do will only select the records with a timestamp of Monday or Friday, no matter what week of the year we are in.

 

[SkipVought]

ya know, I missed the CURRENT WEEK criteria -- SORRY!

[tt]
Int(([YourDateField]/7)*7) = Int(([Date]/7)*7) AND Format([YourDateField],"ddd") In ("Mon","Tue","Wed","Thu","Fri")
[/tt]
???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[UNDPC]

Skip,
I think we're almost there. It is prompting me for the [Date] parameter, so it is not reading the current date. I tried creating another column in the query like:

Date: Date()

and using [Date], but I guess since it is a column that is added only in the query it will not pick up the value. Any ideas?

 

[UNDPC]

Skip,
I have this worked out on an excel worksheet using code like this:

Int(((Date())-1)/7)*7+2 for current week Monday

And

Int(((Date())-1)/7)*7+6 for current week Friday

But when I try to do it like this in the Access query, the database gives me an error of "data type mismatch".
Does this help at all?

 

[SkipVought]

Aren't you putting this criteria in a date column that will contain dates in the current week?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Here's how the current date van be formatted
[tt]
Int(([MyDate]/7))=Int((Now()/7)) And Format([MyDate],"ddd") In ("Mon","Tue","Wed","Thu","Fri")
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

The formula Int(((Date())-1)/7)*7+2 returns an Integer which is not compatible with a Date (which is a Double). To get a value for the (current or) previous Monday which is comparable with a Date, use ..

[tt][blue]Date()-Weekday(Date(),2)+1[/blue][/tt]

getting a value for the Friday is more awkward because it may be in the past or the future; as good as any way is to add 4 to the Monday ..

[tt][blue]Date()-Weekday(Date(),2)+5[/blue][/tt]

Putting them together, you should be able to use ,,

[tt][blue]WHERE YourDate BETWEEN Date()-Weekday(Date(),2)+1 AND Date()-Weekday(Date(),2)+1[/blue][/tt]



Enjoy,
Tony

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

 

[TonyJollans]

Hi UNDPC,

Thought about it after posting, and if you have times with your dates, you probably want to check for them being between Monday and Saturday. The actual comparands are midnight at the start of the Monday and Friday, so later in the day on Friday will not be included by the solution I gave; you might want to try ..

[blue][tt]WHERE YourDate BETWEEN Date()-Weekday(Date(),2)+1 AND Date()-Weekday(Date(),2)+[red]6[/red][/tt][/blue]

- When I went to copy this from my earlier post, I found I had posted the wrong end date and given you a clause which would most probably have returned nothing. Sorry - hope you see this before you use that.

Enjoy,
Tony

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

 

[SkipVought]

Tony,

The purpose of the experssion is NOT to return a date but rather to COMPARE TWO dates (Ture/False)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi Skip,

Just checked back and in my reading of the thread, UNDPC is asking for criteria to select records where 'his' date is between the Monday and the Friday of the current week, which is what I've given, isn't it? Actually I've given SQL - to use as criteria in Query design it doesn't need the "WHERE Yourdate" bit, just ..

[blue][tt]BETWEEN Date()-Weekday(Date(),2)+1 AND Date()-Weekday(Date(),2)+6[/tt][/blue]

He's got one date; he's asking for a formula to produce another (two) to compare it against. The formula has to return a Date (or something comparable to a Date) in order to be used in the comparison. I don't see what I've misunderstood. But, then, it is Friday and I've just re-istalled Windows, again

Enjoy,
Tony

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

 

[SkipVought]

Tony,

You replied above...

"The formula Int(((Date())-1)/7)*7+2 returns an Integer which is not compatible with a Date (which is a Double)."

I had just posted the criteria expression...

Int(([MyDate]/7))=Int((Now()/7)) And Format([MyDate],"ddd") In ("Mon","Tue","Wed","Thu","Fri")

Maybe I misunderstood the object of your comment.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884