Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

AccessTable - Edit Dates

I have a table that lists dates. How would I query a second column that shows the Friday for the week each date falls in to?

Assume each week starts on Saturday and ends on Friday.
Assume Table Name: "tblListDates", FieldName: "DateList"

i.e. My table may have the following dates. (In reality there is 500K records spanning from 1998)
   
Date List      New Column I would like (Shows Friday of every week date falls into.)
1/1/09          1/2/09
1/2/09          1/2/09
1/3/09          1/9/09
1/4/09          1/9/09
1/5/09          1/9/09
1/6/09          1/9/09
2/12/09        2/13/09
2/17/09        2/20/09
5/1/09          5/1/09
5/2/09          5/8/09
5/3/09          5/8/09

Notice how in the new column (Second Column) each date is a Friday. This represents the Friday of the week the date in the first column falls into (Assuming every week starts on Saturday and Ends on Friday.)
0
ouestque
Asked:
ouestque
1 Solution
 
Rey Obrero (Capricorn1)Commented:
select DateList,dateadd("d",6-weekday([DateList]),[DateList]) as NewColumn
from tblListDates


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now