Query Criteria (97 SR2)

Topic

New Reply

This may be a no-brainer for you Access Gurus, but here’s what I want to do:

A query is run against a set of data. This data contains a field for ‘Date’. I want to display only the data that was generated between Friday and Friday. So today, being Monday, it would only show records generated from the 3rd of august, to the 10th. (Friday to Friday). If I run it on Thursday, it will show the same data set. If I run it on Friday, it will show the records from the 10th to the 17th.

Ideally this will be automatic, but if it’s not possible, I can force the user to specify dates..

Is this possible?

Thanks!

Reply | Quote

Replies

Maybe not the most eloquent formula, but it’s a start. Try the following:

In the query – for the criteria row of the field that has the date, type in the following:

Between ((Date()-Choose(DatePart(“w”,Date()),2,3,4,5,6,7,0))-7) And (Date()-Choose(DatePart(“w”,Date()),2,3,4,5,6,7,0))

NOTE: This is as you requested in your posting. However, this will actually include 8 days worth of data. If you’re only interested in one week’s worth of data you’d need to decide which end of the formula to deduct a day from.

Reply | Quote

Thanks TJ!

Say, what part of this formula is determining that the data retrieved is done so on Friday? I wouldn’t be surprised if my boss decided that Monday would be better…

Thanks again!

Reply | Quote

Anyone?

Reply | Quote

Sorry I didn’t answer sooner, but I didn’t get back to the board until today.

==============
Original Formula
==============
Between ((Date()-Choose(DatePart(“w”,Date()),2,3,4,5,6,7,0))-7) And (Date()-Choose(DatePart(“w”,Date()),2,3,4,5,6,7,0))

==============
Explanation
==============
The DatePart(“w”,Date()) of the formula returns an integer indicating which day of the week the date is, 1=Sunday through 7=Saturday.

The Choose() portion returns how many days to subtract from the current Date(). The number of days is determined by the integer returned from the DatePart() function.

For Example: If the date is a Sunday, DatePart() returns a 1. This then causes Choose() to select the first item in the list of 2,3,4,5,6,7,0.

So, to adjust the formula to use for Monday vs. Friday you’d need to adjust the Choose() select list to:
6,0,1,2,3,4,5

So the revised formula for Monday’s would be:

==============
Revised Formula
==============
Between ((Date()-Choose(DatePart(“w”,Date()),6,0,1,2,3,4,5))-7) And (Date()-Choose(DatePart(“w”,Date()),6,0,1,2,3,4,5))

HTH

Reply | Quote

thank you kindly!

Reply | Quote

You are quite welcome.

I’m still waiting to see though if there someone else has a cleaner method.

Reply | Quote

Alas, I was right.. Wed to Wed.. The “0” in your formula is confusing me…

Reply | Quote

OOOPS.

The reason the zero was probably confusing you is I discovered it (the zero) was misplaced in the initial formula. The zero should be located in the Choose() list for the # related to the day of the week. So, if you wanted to work with Sunday-Sunday, since Sunday equates to the integer 1, the zero should be in the first position. For Wednesdays, the zero should be in the 4th position, i.e.

Date()-Choose(DatePart(“w”,Date()),4,5,6,0,1,2,3)

Clear as mudd?

Reply | Quote

I use the statement
endofweek(DateAdd(“d”,Date(),-7),7)
as the criteria for the date field.

endofweek is a function that returns the date representing the last day of the current week.

Function EndOfWeek(D As Variant, Optional FirstWeekday As Integer) As Variant
‘
‘ Returns the date representing the last day of the current week.
‘
‘ Arguments:
‘ D = Date
‘ FirstWeekday = (Optional argument) Integer that represents the first
‘ day of the week (e.g., 1=Sun..7=Sat).
‘
If IsMissing(FirstWeekday) Then ‘Sunday is the assumed first day of week.
EndOfWeek = D – WeekDay(D) + 7
Else
EndOfWeek = D – WeekDay(D, FirstWeekday) + 7
End If
End Function

Reply | Quote