Weekday function - 'between this Tuesday and Last Tuesday'

[dgmoore]

I've hit a snag - I know this is easy, but the logic is escaping me. I
need to set criteria in a query to find dates in a date field that lie
between Tuesday of the current week and Tuesday of the previous week.
Any suggestions would be appreciated.

Thanks
Dave

[CDMAPoster]

On Feb 8, 3:38 pm, dgmo...@erols.c om wrote:

I've hit a snag - I know this is easy, but the logic is escaping me. I
need to set criteria in a query to find dates in a date field that lie
between Tuesday of the current week and Tuesday of the previous week.
Any suggestions would be appreciated.

Thanks
Dave

See:

http://groups.google.com/group/comp....ee3a7c7ac10abf

for a way to get the date of the beginning of the week (untested).
Call it dtX.

After that, you'll want the date of the Tuesday on or after that
date. From my GEDay function applied to Tuesday (air code),

dtTues = DateAdd("d", (10 - WeekDay(dtX)) Mod 7, dtX)

Then you'll need the date of the Tuesday one week prior to that:

dtPrevTues = DateAdd("d", -7, dtTues)

I hope this helps,

James A. Fortune
CD********@Fort uneJames.com

[CDMAPoster]

On Feb 8, 4:46 pm, CDMAPos...@Fort uneJames.com wrote:

On Feb 8, 3:38 pm, dgmo...@erols.c om wrote:

I've hit a snag - I know this is easy, but the logic is escaping me. I
need to set criteria in a query to find dates in a date field that lie
between Tuesday of the current week and Tuesday of the previous week.
Any suggestions would be appreciated.

Thanks
Dave

See:

http://groups.google.com/group/comp..../msg/1aee3a7c7...

for a way to get the date of the beginning of the week (untested).
Call it dtX.

After that, you'll want the date of the Tuesday on or after that
date. From my GEDay function applied to Tuesday (air code),

dtTues = DateAdd("d", (10 - WeekDay(dtX)) Mod 7, dtX)

Then you'll need the date of the Tuesday one week prior to that:

dtPrevTues = DateAdd("d", -7, dtTues)

I hope this helps,

James A. Fortune
CDMAPos...@Fort uneJames.com

You can also use my LEDay function to get the date of the first day of
the week.

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Replace vbDay with the Weekday number of the day the week starts on.

Sunday = 1
....
Saturday = 7

Example:

Week starts on Monday
Monday = 2
dtStartOfWeek = DateAdd("d", -(5 + WeekDay(dtIn)) Mod 7, dtIn)

dtIn = #2/8/07#
MsgBox(DateAdd( "d", -(5 + WeekDay(dtIn)) Mod 7, dtIn)) =2/5/2007

James A. Fortune
CD********@Fort uneJames.com

[Ron2006]

Tuesday of this week is always equal to

TuesdayDate = date() - (datepart("w",d ate()) - (3 -
Datepart("w",da te())))

PriorWednesdayD ate = TuesdayDate - 6

then the criteria will be

between PriorWednesdayd ate and TuesdayDate

The between function includes the equal condition for the two end
dates. If you subtracted 7 then the Tuesday dates would be included in
last weeks. search AND in this weeks search - you would have 8 days of
activity not 7 days.
explanation:
datepart("w",da tefield) will supply the day of the week relative to
Sunday being day 1
therefore Tuesday is always 3.
If the day is Sunday or Monday the math will add days to the date.
If the day is tuesday the math will subtract days from the date to
always land on Tuesday.
If the day is Tuesday, the math doesn't change the date.

Ron

[dgmoore]

On Feb 8, 5:40 pm, "Ron2006" <ronne...@hotma il.comwrote:

Tuesday of this week is always equal to

TuesdayDate = date() - (datepart("w",d ate()) - (3 -
Datepart("w",da te())))

PriorWednesdayD ate = TuesdayDate - 6

then the criteria will be

between PriorWednesdayd ate and TuesdayDate

The between function includes the equal condition for the two end
dates. If you subtracted 7 then the Tuesday dates would be included in
last weeks. search AND in this weeks search - you would have 8 days of
activity not 7 days.

explanation:
datepart("w",da tefield) will supply the day of the week relative to
Sunday being day 1
therefore Tuesday is always 3.
If the day is Sunday or Monday the math will add days to the date.
If the day is tuesday the math will subtract days from the date to
always land on Tuesday.
If the day is Tuesday, the math doesn't change the date.

Ron

All great answers, Gents - thanks a lot!
Dave