Calculate date

  • Thread starter Thread starter davidstevans
  • Start date Start date
D

davidstevans

Hi all,

I have the following MyTable:

ID SSN DATE

the date is in the form of 4/10/1970. Month, Day Year.

My question is it display the ID & SSN if date is older than 6
years. Here is my sql but I am not picking up all the records.

SELECT ID, SSNI from MyTable:
where MyTable.date < Date() - 3900

I came up with 3900 by multiplying 365(days) * 6(years).

thanks in advance for all your input
 
365 * 6 = 2190.

Instead, try

SELECT ID, SSNI from MyTable:
where MyTable.date < DateAdd("yyyy", -6, Date())
 
365 * 6 = 2190.

Instead, try

SELECT ID, SSNI from MyTable:
where MyTable.date < DateAdd("yyyy", -6, Date())
Click to expand...


Thanks for the help,

One more question if I was asked the question what is the ID & SSN
if date is older than current years. Will the following query work

SELECT ID, SSNI from MyTable where MyTable.date < DateAdd("yyyy", +1,
Date())

thanks



where MyTable.date < DateAdd("yyyy", +1, Date())
 
Thanks for the help,

One more question if I was asked the question what is the ID & SSN
if date is older than current years. Will the following query work

SELECT ID, SSNI from MyTable where MyTable.date < DateAdd("yyyy", +1,
Date())

thanks

where MyTable.date < DateAdd("yyyy", +1, Date())
Click to expand...

Sorry in the last query I made a typo question was

ID & SSN
if date is greater than current years. Will the following query work
SELECT ID, SSNI from MyTable where MyTable.date > DateAdd("yyyy",
+1,
Date());

thanks
 
Depends. Do you want dates that are more than January 1 of the next year
or do you want dates that are more than one year in from today's date? Your
criteria gets records that are after August 27, 2008 (this being August 27,
2007).

If you want dates that are in 2008 and later then
Where MyTable.Date >= DateSerial(Year(Date()), 1,1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top