Access query expression

[caloy]

Running a query for 6 mos of data and can't remember the expression to
aggregate volume by week, as in:

Week Ending Volume
June 1, 08 100
June 8, 08 80
June 15, 08 120

With Monday as start of week.
I am using a formula = 7-weekday [date] + [date] to get weekly summary but
need a date format to go along with it...as in the example above

Hope this is clear.

 

[Michel Walsh]

Format( 7-weekday(dateTimeStamp) + dateTimeStamp ,"mmmm dd, yyyy")



Vanderghast, Access MVP

 

[caloy]

Michel,
It returns a date but the date reads January 06, 1900. How do I fix that?
--
Caloyski

Format( 7-weekday(dateTimeStamp) + dateTimeStamp ,"mmmm dd, yyyy")



Vanderghast, Access MVP

Running a query for 6 mos of data and can't remember the expression to
aggregate volume by week, as in:

Week Ending Volume
June 1, 08 100
June 8, 08 80
June 15, 08 120

With Monday as start of week.
I am using a formula = 7-weekday [date] + [date] to get weekly summary but
need a date format to go along with it...as in the example above

Hope this is clear.

 

[Michel Walsh]

Your date-time value has a year? If not, add Year(now):


Format( 7-weekday(dateTimeStamp) + dateTimeStamp + Year(now) ,"mmmm dd,
yyyy")



or


Format( 7-weekday(dateTimeStamp) + dateTimeStamp + iif(
Year(dateTimeStamp)=0, Year(now), 0) ,"mmmm dd, yyyy")



Vanderghast, Access MVP

Michel,
It returns a date but the date reads January 06, 1900. How do I fix that?
--
Caloyski

Format( 7-weekday(dateTimeStamp) + dateTimeStamp ,"mmmm dd, yyyy")



Vanderghast, Access MVP

Running a query for 6 mos of data and can't remember the expression to
aggregate volume by week, as in:

Week Ending Volume
June 1, 08 100
June 8, 08 80
June 15, 08 120

With Monday as start of week.
I am using a formula = 7-weekday [date] + [date] to get weekly summary
but
need a date format to go along with it...as in the example above

Hope this is clear.