Creating Dates in a Query

D

davidd

I have an order database that I need the field date to display the Monday
date of the week it was entered. For instance, the order date displays
7/13/2006 when the Monday of that week was 7/10/2006. What function do I need
to use in my Query to return the Monday for the date I'm referencing? I need
it to return 7/10/2006 for any date from 7/10/2006 to 7/16/2006, 7/17/2006
for any date from 7/17/2006 to 7/23/2006, etc. Any suggestions?
 
F

fredg

I have an order database that I need the field date to display the Monday
date of the week it was entered. For instance, the order date displays
7/13/2006 when the Monday of that week was 7/10/2006. What function do I need
to use in my Query to return the Monday for the date I'm referencing? I need
it to return 7/10/2006 for any date from 7/10/2006 to 7/16/2006, 7/17/2006
for any date from 7/17/2006 to 7/23/2006, etc. Any suggestions?
Click to expand...

MondayOfWeek: DateAdd("d",-Weekday([ADate])+2,[ADate])

Note: The correct Monday for the week of Sunday, 7/16/2006, is
7/17/2006 not 7/10.
However, if you want the previous Monday, use
IIf(Weekday([ADate])=1,DateAdd("d",-6,[ADate]),DateAdd("d",-Weekday([ADate])+2,[ADate]))
 
D

davidd

That worked! Thanks for the help.

John Spencer said:
Field: MondayMonday: DateAdd("d",2-WeekDay([OrderDate]),[OrderDate])


davidd said:
I have an order database that I need the field date to display the Monday
date of the week it was entered. For instance, the order date displays
7/13/2006 when the Monday of that week was 7/10/2006. What function do I
need
to use in my Query to return the Monday for the date I'm referencing? I
need
it to return 7/10/2006 for any date from 7/10/2006 to 7/16/2006, 7/17/2006
for any date from 7/17/2006 to 7/23/2006, etc. Any suggestions?
Click to expand...
Click to expand...
 
Top