Last day of previous month

[goslincm]

Good afternoon. I am using the following in my query:

Format(DateSerial(Year(Date()),[FiscalEndMM],1),"mm/"01"/"yyyy")

The problem I have found is that where I have the day of the month setting to 01, I'm told it should be set to the last day of the month.

How can I do that?

 

[mhartman1]

Hello:

=DateSerial(Year(Date()), Month(Date()), 0)

will give you the last day of the previous month based on today's date:

Regards
Mark

 

[PHV]

Format(DateSerial(Year(Date()),[FiscalEndMM]+1,0),"mm/dd/yyyy")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[goslincm]

Thanks Mark, but in looking back I didn't explain as well as I could have. What I am doing, is taking a field called [FiscalEndMM] which represent the month a companies fiscal year ends, and combining that numeric representation of the month, with the current year.

So if there fiscal year ends in January, I would get 01/01/2007. I need however, do be able to pick up the last day of the month for whatever month is being represented in the field [FiscalEndMM].

So in my example above, I need a way to get: 1/31/2007

 

[PHV]

That was my understanding, so simply try my suggestion above.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[goslincm]

Excellent, you folks are SO helpful.