How do I add on dates

[zhuanyi]

Hello,
Say for example one of the fields in a table is in the format of YYYYMMDD (i.e., 20070426 for today), and the number of years is in the format of YY (say 07) and I want to run a query to include only the data that when the years in YYYYMMDD is added with YY, the resulting year is around, say, 2020 and 2021, is there anyway I could build an expression for that? Thanks a lot!

Regards,
Anyi

 

[rainman89]

dateadd("y", [numberfieldyouwantadded], [yourdatefield])

 

[zhuanyi]

But is Access capable of recognizing, say in a string named 20070514, the 2007 is the year instead of 0514? Thanks!

 

[rainman89]

your date field is a string field? if its that case youll probably have to parse the string then add to it.
If you dont want to deal with the parsing you can store it as a date and it would probably make this easier.

 

[zhuanyi]

Mmm...parsing...that does not sounds good, is there anyway I could simply extract the first four digits in YYYYMMDD without using VBA and then convert the four digits to year? Thanks!

 

[rainman89]

thats what parsing is...
why not change the field to a date/time field

 

[zhuanyi]

I am not allowed to change anything on the source table...by company policy...
So how can I do that without using VBA? Thanks!

 

[rainman89]

check here