Setting Default Dates To Prior Month

[EdAROC]

I'm working on a form to prompt for report parameters and run a report. The report has Begin and End dates (and uses popup calendar learned here, thank you). I know that accounting will usually run this report as part of their closing process (the first few days of the next month), therefore, the begin and end dates will be the 1st and last days of the prior month.

The End Date is easily calculated:
End Date =DateSerial(Year(Now()),Month(Now()),1)-1
*Get to the beginning of the current month and subtract one day.

The Begin Date gets a little complex, to calculate Year and Month I use the End Date formula, then use "1" for the first day of the month.
Begin Date =DateSerial(Year(DateSerial(Year(Now()),Month(Now()),1)-1), Month(DateSerial(Year(Now()),Month(Now()),1)-1),1)

Anyone have another technique, especially one that may be easier?

 

[Remou]

How about:

[tt]End Date =DateSerial(Year(Now()),Month(Now()),0)
Start Date =DateSerial(Year(Now()),Month(Now())-1,1)[/tt]

 

[EdAROC]

This is a good time of year to be developing this technique because January is a different situation - Month# is 1 and going back a month also changes the year.

So, your formula would not work in January, here's what happens:
Start Date =DateSerial(Year(Now()),Month(Now())-1,1)
* Evaluates to...
Start Date =DateSerial(2007,0,1)

Nice try. I posted this one to see if someone's figured out a "better solution" (whatever that means in this case). It's a bit tricky, as you now realize.

 

[PHV]

Seems you haven't even tried the excellent Remou's suggestion as DateSerial(2007,0,1)=#2006-12-01# !!!

 

[Remou]

Hm. If these are parameters, why not refer to End_Date?

End_Date =DateSerial(Year(Now()),Month(Now()),0)
Start Date =DateSerial(Year(End_Date),Month(End_Date),1)

If not, how about:
Start Date=dateadd("m",-1,DateSerial(Year(Now()),Month(Now()),1))

 

[EdAROC]

My failure to communicate clearly. My apologies.

I'm setting the default values that will be displayed on the form when it opens.

? I noticed for the end date you used a zero (0). Did a little Googling and learned more about the argument values. THANKS!