Solved

MS Access 2010 Date help

Posted on 2014-12-08
2
197 Views
Last Modified: 2014-12-08
Hi
Need help with the following please.
We have a query that contains a date field. What we cant to do is add 7 days to this date and then show the last 30 days from today.
So for example I have the following

Date in Table           Add 7 Days      
01/09/2014              08/09/2014
01/12/2014              08/12/2014
02/12/2014             09/12/2014

I can get the query to add 7 days and show the correct date, but I also then want the query to only show me records that have a date from today 30 days back based on the new date I have created, so from the example above the would only return 2 records that have a date in December.  I cant seem to do that as I can seem to add a query to the field that creates the new date, If I try and then run the query I get the "enter parameter value"

Thanks
0
Comment
Question by:George_Milton
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40486494
It would help if you'd show the SQL of your query.

Adding 7 days is straight forward:

SELECT Field1, DateAdd("d", 7, YourDateField) AS NewDate FROM SomeTable

This would create a column named NewDate in the query, and would add 7 days to a field named YourDateField.

If you then want to query and filter on YourDateField for values starting at 30 days in the past:

SELECT YourDateField, DateAdd("d", 7, YourDateField) AS NewDate FROM SomeTable WHERE YourDateField >= DateAdd("d" , -30, Now)
0
 

Author Closing Comment

by:George_Milton
ID: 40486502
Thank you that worked exactly as I expected
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Before insert Image (OLE) resize image 11 41
sort Time by AM and PM in query 2 18
Tags from access to excel 3 26
Update fields from multiple tables via same form 2 22
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now