Calculating quarterly volume...

[Ken D.]

I am building a commission database and I am challenged by the syntax for the
calculation. Let me explain. The sales commission is based on a tier system
determined by the quarterly volume. Since commissions are calculated at the
beginning of the month for the previous month, my challenge is that when the
current month is the first month of a quarter, I need the volume for the
previous quarter. Not a problem here until the following month when I need
the volume for the current quarter. I am trying to avoid any action by the
user to change/enter criteria.

What is the syntax for determining the sales volume (by rep) for each
quarter that will compensate for previous quarter, current quarter and even
previous quarter/previous year (Jan challenge).

I hope I did not confuse you.

Any help would be appreciated.

 

[John Spencer]

It sounds as if you need to determine the quarter (and the year) based on
the prior month.

To get a date in the previous month, you can use
DateSerial(Year([SomeDate]),Month([SomeDate])-1,1)
To get the quarter and year, you can use
Format(DateSerial(Year([SomeDate]),Month([SomeDate])-1,1),"YYYY-Q")
OR
DatePart("q",DateSerial(Year([SomeDate]),Month([SomeDate])-1,1)) for the
quarter number
and
Year(DateSerial(Year([SomeDate]),Month([SomeDate])-1,1)) to get the year
number