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.

 

[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