Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

AccessTable - Edit Dates

Posted on 2009-06-30
1
185 Views
Last Modified: 2013-11-29
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
Comment
Question by:ouestque
1 Comment
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24750216
select DateList,dateadd("d",6-weekday([DateList]),[DateList]) as NewColumn
from tblListDates


0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Flowing down data to other tables 13 32
SQL query and VBA 5 45
Access/Visual Basic Question 3 24
Record Lock on Database will not go away 9 20
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question