Access 2007 Date Query Criteria

I need help with a query on one of my tables. On one of the table fields there is a date mm/dd/yyyy. I need to have the user input a future date and then count back 20 years from the table field date.
CMILLERAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Use DateAdd() to subtract 20 years from the entered date.

NewDate = DateAdd("yyyy", -20, YourDate)
0
Rey Obrero (Capricorn1)Commented:
try this format

SELECT ID, DateField
FROM YourTable
WHERE [DateField]=DateAdd("yyyy",-20,[Enter Date])
0
CMILLERAuthor Commented:
Rey,

its not working. here is the format for the date field mm/dd/yyyy
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
post the query you are using.

is the field type Date/Time? or text?

better if you can upload a copy of your db.
0
CMILLERAuthor Commented:
its date/time

sorry, cant upload
0
Rey Obrero (Capricorn1)Commented:
post the query you are using.
0
CMILLERAuthor Commented:
SELECT [table1].datefield
FROM [table1]
WHERE ((([table1].[datefield])=DateAdd("yyyy",-20,[Enter Date])));
0
CMILLERAuthor Commented:
it asks for the date but doesnt return anything
0
Rey Obrero (Capricorn1)Commented:
is the name of the field with dates "datefield" ?
0
PatHartmanCommented:
Are you sure there is a record with the date you are looking for?
Is datefield defined as a Date data type?
0
Rey Obrero (Capricorn1)Commented:
see this sample db,

enter 1/1/2014 and the query will return records with date 1/1/2013
Q-28515613.accdb
0
CMILLERAuthor Commented:
I am not looking for a specific date that matches -20 years.

I want to be able to enter a date ( 01/14/2015 ) and find all records or dates that go back +20 years.

so, it would find ALL dates that are before 01/14/1995
0
CMILLERAuthor Commented:
how do I download the accdb file? I have ran across this before. You can upload one but i think there is an issue with EE on downloading accdb files. am I correct?
0
PatHartmanCommented:
Then you can't use = as the relational operator.  Your query is looking for a specific date.  Between will probably get what you want.

WHERE ((([table1].[datefield]) Between DateAdd("yyyy",-20,[Enter Date]))) AND [Enter Date];
0
PatHartmanCommented:
I've complained about the download issue but not gotten a resolution.  Since I can't normally download files anyway, I never pushed the issue but sometimes it is nice to actually see what everyone is talking about.
0
CMILLERAuthor Commented:
I am getting an error "between operator without And in query expression"
0
PatHartmanCommented:
It probably has to do with all the excess parentheses.  Get rid of all them except the ones needed for the DateAdd() function.
0
Rey Obrero (Capricorn1)Commented:
<so, it would find ALL dates that are before 01/14/1995 >

would have been a lot easier if you clarified this criteria


run query1
Q-28515613.mdb
0
Rey Obrero (Capricorn1)Commented:
sorry use this, run query2
Q-28515613.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CMILLERAuthor Commented:
Rey, I apologize that it wasnt clear.
0
CMILLERAuthor Commented:
Rey, the second query worked.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.