Special date question.

Hans

Registered User.
Local time
Today, 08:24
Joined
Feb 6, 2002
Messages
20
Does anyone know of a way in a query to have criteria that takes [date] as a parameter, and returns the date but with a year less than what was typed. For example I type in 1/6/02 and it returns 1/6/01.

I can have a [date]-365, but that doesn't work. I need the exact date from the previous year. Thanks alot.
 
Try placing this formula in the date criteria of the field you are trying to retrieve the previous year's date: CDate(Month([Date:]) & "/" & Day([Date:]) & "/" & Year([Date:])-1)

HTH,

Edward
 
Give me a day to think of a workaround for leap years.
 
Did you try:

1. to declare [Enter a Date] as a parameter of Date/Time Type using the parameters menu of the QBE grid (avoid using names like Date that are reserved words)

2 to use in your query:
dateserial(Year([Enter a Date])-1, Month([Enter a Date]), Day([Enter a Date]))

Hope this helps.

Alex

[This message has been edited by Alexandre (edited 02-22-2002).]
 
At the risk of being obvious, did you try:
DateAdd("y",-1,[Enter date])
?

David R
 
Didn't try that. Pretty good. However, the correct syntax seems to be DateAdd("yyyy",-1,[Enter date]) instead of DateAdd("y",-1,[Enter date]). Also, it doesn't seem to work with leap years where the user may want to check the compare the last day of the month of the current year with the previous year. For example, if you want to check the previous leap year of 1985, you can not check either 2/28/1984 and/or 2/29/1984 with the DateAdd method. You can only check 2/28/1984. You CAN, however, with this method:

IIf(Month([Date:])=2 And Day([Date:])=29,CDate(Month([Date:]) & "/" & Day([Date:])-1 & "/" & Year([Date:])-1),CDate(Month([Date:]) & "/" & Day([Date:]) & "/" & Year([Date:])-1))
 
Good point about leap years, however you can still use DateAdd() rather than CDate() and the rest. Just use the day switch (this one really is "d") and 365/366 in your Iif statement.

No need to reinvent the wheel.
biggrin.gif


I'm not sure why DateAdd("y",-1... didn't work on your computer. I tried this one out on mine (Acc2k) and it worked fine.

David R

[This message has been edited by David R (edited 02-25-2002).]
 
This date issue was a real sticking point,but now your ideas are helping a ton, and I will soon move past it. Thanks so much!
 
No problem Hans, And thank you Dave for spreading the knowledge.
 

Users who are viewing this thread

Back
Top Bottom