Subtract months from the years and months field

[FahadTiger]

Hi ,Expert
I have 3 field in query as shown ...I have yearNu and monthNub ... and subtractMomonth ..when I enter 15 in subtractMomonth ..the resault is YearNu=18 and MonthNu=11...is there function to do do this
thanks for all

 

[plog]

When you want to work with dates, you need to work with dates. Do that you have a lot of built in date functions available to you:

MS Access: Functions - Listed by Category

For easy reference, we have provided a list of all Microsoft Access functions sorted into categories such as string, numeric/mathematical, date/time, advanced/logical, domain aggregate, data type conversion, group by, and file/directory functions.

www.techonthenet.com

DateAdd would be a simple function to use in this instance, however, it works with dates, not numbers like you have. Instead of 2 fields--one for month and one for year, you should store the MonthNu and YearNu in one field that is a date/time field. Do that and Access can easily subtract 15 months from February 1, 2020 to arrive at November 1, 2018.

The alternative is to write your own function to achieve the same thing.

 

[XPS35]

Use DateSerial (with fictional day) to convert to date, DateAdd to subtract and Year() and Month te convert to numbers again.

 

[Pat Hartman]

is there function to do do this
thanks for all

No. Not directly. First you have to convert your string to an actual date. Then you can use the DateAdd() function to add/subtract months.

Rather than making work for yourself, it may be simpler as @plog suggested to just keep the year/month as a valid date with a value of 1 for the day. You can format forms and reports to just show the month and year.

PS, Just FYI, the developers in the 1970's never thought they'd run into a problem by storing the year as two digits but having to convert the two digit year to 4 to make your two fields into a valid date, I wouldn't rely on MS to get it right. They will make an assumption as to what century you want and it may not be correct so, I would include that in the calculation also.

PPS, who knew in the early 80's, I'd end up with two applications that are alive and well in the 21st century but I do. Good thing I used 4-digit years and knew how to calculate leap year correctly so they didn't fail due to the Y2K problem in 2000 I'm pretty sure they won't make it to the next century mark but they've had a really good run and kept air travellers safer all this time.

 

[theDBguy]

Hi ,Expert
I have 3 field in query as shown ...I have yearNu and monthNub ... and subtractMomonth ..when I enter 15 in subtractMomonth ..the resault is YearNu=18 and MonthNu=11...is there function to do do this
thanks for all

I'm not sure I understand the question. Where will you enter the value 15? In the query? And how come the result will equal to 18 and 11, instead of 20 and 2?

 

[arnelgp]

when I enter 15

maybe you mean 50?
coz:

Debug.Print DateAdd("m", -50, Date)

Nov 18, 2018

Nov = 11
2018 last 2 digit is 18