need to generate a date

[lshippee]

I need to generate a date that is the last day of the month from a given, changeable start date based on two optional time periods, 18 or 36 months. That is, the start date is entered in one field, the number of months for the time period is in another. The third field would contain the last day of whatever month occurs when the time period is added to the start date.

Access help showed me a function called EOMONTH - eomonth(start_date,months)
which seemed would do exactly what I want. I got the error message "undefined function in expression". The functions reference list in help which shows all usable functions does not list eomonth.

Is my syntax wrong? In a the query I used:

Date: eomonth([start date from field in query],[number of months from field in query])

Any other suggestions for how to accomplish what I want?
Leslie Shippee
Information Analyst

 

[braindead2]

play around with the date serial function

dateserial(year(date()),month(date())+36+1,1)-1

whaat I'm doing here is adding 36 months +1 month to give me next moths date, the I set the date to the first day then subtract 1 which ill give the first day of the previous month

?dateserial(year(date()),month(date())+36+1,1)-1
12/31/2004

 

[lshippee]

Thanks for the reply. I thought dateserial relies upon the system date. I need to generate the date based on user input. Can you enlighten me further? Leslie Shippee
Information Analyst

 

[braindead2]

look it up in help!

if you use it like this then I think you will be happy with the results
dateserial(year([start date from field in query]),month([start date from field in query])+[number of months from field in query]+1,1)-1