This gave me the previous quarter but what I'm wanting is the previous year's
quarter(e.g. Q1 of 2009). I tried working with it but I can't seem to get
the logic. Please help!

Thank you again.


"vanderghast" wrote:

Either you change the value in

FORMS!frmVarianceQtr!txtMo1

to reflect a year in last date, either you subtract 1 from Year(
CDate(FORMS!frmVarianceQtr!txtMo1) )

to obtain:

iif(Year(transDate) = Year(CDate(FORMS!frmVarianceQtr!txtMo1)) -1
AND DatePart("q", transDate) = DatePart("q",
CDate(FORMS!frmVarianceQtr!TxtMo1 )) ,

ODC_cost,

0 )



Vanderghast, Access MVP



"AccessKay" wrote in message
...
This works...thankyou! I apologize for being greedy but how can I change
this to show the previous year's quarter?

"vanderghast" wrote:

assuming you have a dateTime and not a string, you can change the
comparison
to test the year and the quarter:

iif( Year(transDate) = Year( CDate(FORMS!frmVarianceQtr!txtMo1) )
AND DatePart("q", transDate) = DatePart("q",
CDate(FORMS!frmVarianceQtr!TxtMo1 )) ,

ODC_cost,

0 )



You could also have tested:

iif( Format( transDate, "yyyyq") = Format( CDate(
FORMS!frmVarianceQtr!txtMo1 ), "yyyyq"),
ODC_cost,
0)


but using strings is generally slower.



Vanderghast, Access MVP


"AccessKay" wrote in message
...
I have this expression in a calculated field in my query and it works
fine
for the month. I enter the month via a text box on an unbound form. I
thought that if I changed the “m” to a “q” that it would calculate the
quarter based on the date that I supply in my text box. It works if
the
month is the first month in the quarter but if it’s like Feb or March,
it
returns the wrong amount. Can anyone help me fix this so that it pulls
the
correct quarter total?

Qtr_1: Sum(IIf([TransDate] Between ([Forms]![frmVarianceQtr]![txtMo1])
And
DateAdd("q",1,([Forms]![frmVarianceQtr]![txtMo1]))-1,[tblTrans_Mstr].[ODC_Cost],0))

TransDate is in datetime mm/dd/yyyy format

I appreciate any help!