How to determine the begindate of previous month

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

I have an invoice date 03/06/2004 (invoice of March).
Now I would like to determine the begindate of the
previous month, i have tried the following:

DateBegCom = DateAdd("m", "0", [dateinvoice]) - DateAdd
("m", "1", [dateinvoice])

When I execute this I get 29/11/1899 when I actually
should get 02/01/2004 (1st of February, 2004).

What am I doing wrong here? - Thanks.
 
Try this function

Function FirstOfPreviousMonth() As Dat
Dim dtm As Dat
dtm = [dateinvoice
FirstOfPreviousMonth = DateSerial(Year(dtm), Month(dtm) - 1, 1
End Function
 
I have an invoice date 03/06/2004 (invoice of March).
Now I would like to determine the begindate of the
previous month, i have tried the following:

DateBegCom = DateAdd("m", "0", [dateinvoice]) - DateAdd
("m", "1", [dateinvoice])

When I execute this I get 29/11/1899 when I actually
should get 02/01/2004 (1st of February, 2004).

What am I doing wrong here? - Thanks.
Click to expand...

A couple of things!

If you look at the value of DateAdd("m", "0", [dateinvoice]) it will
always be just [dateinvoice] - you're adding zero months to the value
in dateinvoice.

Then, you're subtracting two dates a month apart. This will give a
number, 28 to 31 days, depending on which month. Since a DateTime
value is stored as a Double Float count of days since (or before)
midnight, December 30, 1899, a value such as -29 will indeed
correspond to 29 days before that date - November 11.

Just use

DateBegCom: DateSerial(Year([dateinvoice]), Month([dateinvoice]) - 1,
1)
 
Back
Top