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!

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" <(E-Mail Removed)> wrote in message
news:3AEABEF2-1C02-4781-BF43-(E-Mail Removed)...
>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!
>

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" <(E-Mail Removed)> wrote in message
> news:3AEABEF2-1C02-4781-BF43-(E-Mail Removed)...
> >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!
> >
>

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" <(E-Mail Removed)> wrote in message
news:8501FB12-6470-4F41-B0E7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:3AEABEF2-1C02-4781-BF43-(E-Mail Removed)...
>> >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!
>> >
>>

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" <(E-Mail Removed)> wrote in message
> news:8501FB12-6470-4F41-B0E7-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:3AEABEF2-1C02-4781-BF43-(E-Mail Removed)...
> >> >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!
> >> >
> >>
>

Have you applied the -1 to the Year( ) ? (or to the quarter, Datepart("q",
.... ) ) .


As example, if FORMS!frmVarianceQtr!txtMo1 holds the value 2010.05.02, then

YEAR(FORMS!frmVarianceQtr!txtMo1) would be 2010

DatePart("q", CDate(FORMS!frmVarianceQtr!TxtMo1 )) will be 2,

and finally, the comparison will be (when all constants are finally fully
evaluated) :

iif( Year(transDate) = 2009 AND DatePart("q", transDate) = 2,
ODC_cost, 0 )

so the data considered, inside the true part of that iif, would be from
records where transDate is in 2009, second quarter, Note that the WHERE
clause is applied BEFORE that iif is computed... So if there is a WHERE
clause, in your query, which removes all data from 2009, you will only get 0
from what if left, once it is time to evaluate that iif.



Vanderghast, Access MVP

"AccessKay" <(E-Mail Removed)> wrote in message
news067C40C-437E-47C0-BD11-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:8501FB12-6470-4F41-B0E7-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:3AEABEF2-1C02-4781-BF43-(E-Mail Removed)...
>> >> >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!
>> >> >
>> >>
>>

I appreciate you explaining this. It helped a little to understand what
these statements mean but I’m not clear about the last part regarding the
WHERE statement. I think you’re saying that if you have a WHERE clause in
front of the IIF statement, then some action happens first…but then I get
lost. I don’t have a WHERE but I do have a SUM. I think I need a -1 after
the YEAR…as you indicated. That will give me 2009 but I’m wondering why it
gave me QTR4. Actually, I just tested by putting a 2009 date into the
textbox and now it’s not returning any records at all (2010 dates work fine).
Yikes!!! I’m so confused.

"vanderghast" wrote:

> Have you applied the -1 to the Year( ) ? (or to the quarter, Datepart("q",
> ... ) ) .
>
>
> As example, if FORMS!frmVarianceQtr!txtMo1 holds the value 2010.05.02, then
>
> YEAR(FORMS!frmVarianceQtr!txtMo1) would be 2010
>
> DatePart("q", CDate(FORMS!frmVarianceQtr!TxtMo1 )) will be 2,
>
> and finally, the comparison will be (when all constants are finally fully
> evaluated) :
>
> iif( Year(transDate) = 2009 AND DatePart("q", transDate) = 2,
> ODC_cost, 0 )
>
> so the data considered, inside the true part of that iif, would be from
> records where transDate is in 2009, second quarter, Note that the WHERE
> clause is applied BEFORE that iif is computed... So if there is a WHERE
> clause, in your query, which removes all data from 2009, you will only get 0
> from what if left, once it is time to evaluate that iif.
>
>
>
> Vanderghast, Access MVP
>
> "AccessKay" <(E-Mail Removed)> wrote in message
> news067C40C-437E-47C0-BD11-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:8501FB12-6470-4F41-B0E7-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:3AEABEF2-1C02-4781-BF43-(E-Mail Removed)...
> >> >> >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!
> >> >> >
> >> >>
> >>
>

I got it! Well actually you got it and I just found out how to use it.
Thank you for your help.


"AccessKay" wrote:

> I appreciate you explaining this. It helped a little to understand what
> these statements mean but I’m not clear about the last part regarding the
> WHERE statement. I think you’re saying that if you have a WHERE clause in
> front of the IIF statement, then some action happens first…but then I get
> lost. I don’t have a WHERE but I do have a SUM. I think I need a -1 after
> the YEAR…as you indicated. That will give me 2009 but I’m wondering why it
> gave me QTR4. Actually, I just tested by putting a 2009 date into the
> textbox and now it’s not returning any records at all (2010 dates work fine).
> Yikes!!! I’m so confused.
>
> "vanderghast" wrote:
>
> > Have you applied the -1 to the Year( ) ? (or to the quarter, Datepart("q",
> > ... ) ) .
> >
> >
> > As example, if FORMS!frmVarianceQtr!txtMo1 holds the value 2010.05.02, then
> >
> > YEAR(FORMS!frmVarianceQtr!txtMo1) would be 2010
> >
> > DatePart("q", CDate(FORMS!frmVarianceQtr!TxtMo1 )) will be 2,
> >
> > and finally, the comparison will be (when all constants are finally fully
> > evaluated) :
> >
> > iif( Year(transDate) = 2009 AND DatePart("q", transDate) = 2,
> > ODC_cost, 0 )
> >
> > so the data considered, inside the true part of that iif, would be from
> > records where transDate is in 2009, second quarter, Note that the WHERE
> > clause is applied BEFORE that iif is computed... So if there is a WHERE
> > clause, in your query, which removes all data from 2009, you will only get 0
> > from what if left, once it is time to evaluate that iif.
> >
> >
> >
> > Vanderghast, Access MVP
> >
> > "AccessKay" <(E-Mail Removed)> wrote in message
> > news067C40C-437E-47C0-BD11-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > >> news:8501FB12-6470-4F41-B0E7-(E-Mail Removed)...
> > >> > 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" <(E-Mail Removed)> wrote in message
> > >> >> news:3AEABEF2-1C02-4781-BF43-(E-Mail Removed)...
> > >> >> >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!
> > >> >> >
> > >> >>
> > >>
> >