Calculting months

  • Thread starter Thread starter Danny J
  • Start date Start date
D

Danny J

Dear all,

I have an invoice report where the invoice number is the number of months
since the first order. I have a FirstOrder field with a date, date/time
type. I am confused as to how to calculate the field for the invoice
number. =Date()-FirstOrder would (I think) give the number of days, but
since the number of days in a month is variable I'm stuck. Any suggestions
gratefully received!

Thanks,

Danny
 
Although I admit that I am hesitant about the way you "assign" an invoice
number (what happens if there is more than one invoice per month?), you can
use the DateDiff function to get the number of months between two dates:

DateDiff("m", FirstOrder, Date())

Above will give you the number of months from FirstOrder to Date(). Note
that this will base the calculation on the month number in the dates --
thus, it shows a one-month difference between January 31, 2004 and February
1, 2004.
 
Dear Ken,

I know what you mean but chargees are batched and sent once a month in a
single invoice. Bizarrely the solution you gave did not work for me...I
have been thru help and it all looks fine, but all I get is an error
message. Any thoughts?

Thanks for all your help,

Danny
 
Solved it Ken!

The problem was that the formula had to be an expression in the query.
Muggins here had put it straight into the report and it did not
work......now I have a new problem :-)

Thanks for your help !

Danny
 
Back
Top