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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
sorry use this, run query2
Q-28515613.mdb
0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CMILLERAuthor Commented:
Rey,

its not working. here is the format for the date field mm/dd/yyyy
0
 
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
 
PatHartmanConnect With a Mentor Commented:
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
 
CMILLERAuthor Commented:
Rey, I apologize that it wasnt clear.
0
 
CMILLERAuthor Commented:
Rey, the second query worked.

Thanks.
0
All Courses

From novice to tech pro — start learning today.