ms access - How to get start and end of previous month in VB

Question

Im trying to create some VB code that will get the start and end of the previous month. Im able to the current month which is just:

Month(DateValue(Now))

which would return 3. From there I can take away 1 to give me 2 meaning February. This is fine but what about when I Im in January and I repeat this and it gives me zero - my code will fail. Any one know how to get the previous months start and end day then?

Thanks

Answer 1

The first day of the previous month is always 1, to get the last day of the previous month, use 0 with DateSerial:

''Today is 20/03/2013 in dd/mm/yyyy
DateSerial(Year(Date),Month(Date),0) = 28/02/2013 
DateSerial(Year(Date),1,0) = 31/12/2012 

You can get the first day from the above like so:

LastDay = DateSerial(Year(Date),Month(Date),0)
FirstDay = LastDay-Day(LastDay)+1

See also: How to caculate last business day of month in VBScript