Convert General number to "correct" year date data type

[shztexn]

In a query I would like to extract the last ten years. This is what the data looks like:

12/13 (Data type = text)

(Short for 2012/2013 which actually represents 7/1/2012 to 6/30/2013.)

I am able to extract the "12" and turn it into the general number 2012 using: ("20" & Left([TAXYR],2))*1

But how can I convert that to the data type-date so I can include those records in the past ten years from todays date? I was trying to use DateAdd but I think the problem is the data type and where converting to date gives me "1905" or "9/##/1905." I get why it does that, but is there a workaround? Maybe adding 39,785 days?? (2014-1905)*365

Thanks in advance.

[rpeare]

YearStart: cdate("7/1/" & left([taxyr], 2))
YearEnd: cdate("6/30/" & left([taxyr], 2) + 1)
or
YearEnd: cdate("6/30/" & right([taxyr], 2)

[AlexHedley]

You could also use the DateSerial function.

DateSerial(year, month, day)

Here is a tip using it to calculate the last day of the month.
http://www.599cd.com/tips/access/last-day-of-month/

MS: http://office.microsoft.com/en-gb/ac...001228813.aspx

[shztexn]

Thank you, Alex. I will check that out. I was not aware of the function DateSerial!

You could also use the DateSerial function.

DateSerial(year, month, day)

Here is a tip using it to calculate the last day of the month.
http://www.599cd.com/tips/access/last-day-of-month/

MS: http://office.microsoft.com/en-gb/ac...001228813.aspx

[shztexn]

Thank you, rpeare. I actually thought of that. I will investigate whether changing the date to a full date will help.