query data for last monday through friday

[Guest]

I need to set up my query so that it automatically retrieves data for Mon -
Fri of the previous week, regardless of if it is run on a Mon or Tues or Wed
of the current week. I'm not a technical wizard, so any help...spelled out
as if I were a 5 year old...would be very helpful. Thanks.

 

[Arvin Meyer]

I need to set up my query so that it automatically retrieves data for Mon -
Fri of the previous week, regardless of if it is run on a Mon or Tues or Wed
of the current week. I'm not a technical wizard, so any help...spelled out
as if I were a 5 year old...would be very helpful. Thanks.

You need 2 functions. Put them in a standard module :

Function EndOfLastWeek() As Variant
Dim dtmTemp As Variant
dtmTemp = Date
While WeekDay(dtmTemp) <> 6
dtmTemp = dtmTemp - 1
Wend
EndOfLastWeek = dtmTemp
End Function

and

Function FirstOfLastWeek() As Variant
FirstOfLastWeek = EndOfLastWeek() - 7
End Function

The your query should look something like:

SELECT * FROM MyTable
WHERE DateField Between FirstOfLastWeek() And EndOfLastWeek();
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

You need 2 functions. Put them in a standard module :

Function EndOfLastWeek() As Variant
Dim dtmTemp As Variant
dtmTemp = Date
While WeekDay(dtmTemp) <> 6
dtmTemp = dtmTemp - 1
Wend
EndOfLastWeek = dtmTemp
End Function

and

Function FirstOfLastWeek() As Variant
FirstOfLastWeek = EndOfLastWeek() - 7
End Function

The your query should look something like:

SELECT * FROM MyTable
WHERE DateField Between FirstOfLastWeek() And EndOfLastWeek();
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access


Thank you, Arvin. I have built the module, but I'm still not clear on how to build the query.

 

[Arvin Meyer]

how to build the query.

Replace the field and table names with your own and paste the above ionto an
SQL window. On a new query, don't chose a table and you'll see an SQL button
on the toolbar, or just go to the View menu and choose SQL Then paste your
code in the window, replacing what's there.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[peregenem]

You need 2 functions.

No such need

SELECT
CDATE(DATE() - WEEKDAY(DATE(),2) + 1 - 7) AS monday,
CDATE(DATE() - WEEKDAY(DATE(),2) + 1 - 2) AS friday

Something like

SELECT * FROM MyTable
WHERE date_col >= CDATE(DATE() - WEEKDAY(DATE(),2) + 1 - 7)
AND date_col <= CDATE(DATE() - WEEKDAY(DATE(),2) + 1 - 2)

 

[Arvin Meyer]

No such need

So you are essentially substituting 2 functions for 2 other functions. There
probably are several other ways as well. I prefer a named function because
it can be reused as often as you wish without ever having to rebuild the
expression. YMMV.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[peregenem]

So you are essentially substituting 2 functions for 2 other functions. There
probably are several other ways as well. I prefer a named function because
it can be reused as often as you wish without ever having to rebuild the
expression.

Your named function will cause the code to blow up if called from
outside Access e.g. linking to Access databases from Excel is very
popular. I guess yours makes for cleaner code and easier maintenance,
though.