DateAdd not working correctly. Need fast help!!

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

DateAdd("d",1-Weekday([Start_Date]),[Start_Date])

This is in my query formula. What I really need is to spit the data out
that is between Monday and Friday based on the Start_Date. So if I have 5
things going on between Monday and Friday, I want to display those 5 things
with the column header of MONDAY! Also, i need it to be in Medium Date
Format.

Please let me know the solution as soon as possible.

Thank you,
Ben
 
Ben said:
DateAdd("d",1-Weekday([Start_Date]),[Start_Date])

This is in my query formula. What I really need is to spit the data out
that is between Monday and Friday based on the Start_Date. So if I have 5
things going on between Monday and Friday, I want to display those 5 things
with the column header of MONDAY! Also, i need it to be in Medium Date
Format.
Click to expand...


I'm sure that DateAdd is working as it should. What you
have will display the Sunday of the week containing
StartDate. If you want Monday to be the start of the week,
then you need to specify that:

DateAdd("d", 1 - Weekday([Start_Date], 2), [Start_Date])

Formatting is another issue. If you are displaying Monday's
date in a form/report text box or a query field, then just
set the Format property to however you want it formatted.
If for some reason (crosstab query??), you have to format
the calculated date in the calculation, then use something
like:

Format(DateAdd("d", 1 - Weekday([Start_Date], 2),
[Start_Date]), "Medium Date")
 
Try this --
Format(DateAdd("d",2-Weekday([Start_Date]),[Start_Date]),"dd-mmm-yy")
 
Back
Top