Help! Trouble converting values with IIF statement

  • Thread starter Thread starter Axess08
  • Start date Start date
A

Axess08

I am trying to create a query (query 2) that I will later use in a report. I
have 2 queries that are for used in my question. The idea is that I want to
group values by both State and by Year (in the end in a report) and average
the values that fall under a specific state and year. The averaging is of
course easy, but the grouping is not.

The 1st st query has a number of datediff and year calculations that will be
used in a number of other queries (i.e. Time to Arrival, FY2006, etc.), but
for the purpose of this question it will be used just for the 2nd query. The
1st query has the following calculation headings that will be referenced in
the 2nd query:
Time to Arrival (hrs) (Date diff function)
Time to Cooling (hrs) (Date diff function)
Age when MRI Done (days) (Date diff function)
FY2006 (which is an IIF statement that displays 1 if DOB is within the range
of the fiscal year, i.e. FY2006: Abs(([DOB]>#6/30/2005#) And
([DOB]<#7/1/2006#)) )
FY2007 (Similar IIF statement as above, but different range of dates)
FY2008 (Similar IIF statement as above, but different range of dates)
Current Qtr 1 (Similar IIF statement as above, but different range of dates)
Total (Similar IIF statement as above, but total range of dates)

Query 2 has a number of headings that have values that will later be
averaged and should be "grouped by" year and "Hospital State".
Time to Arrival
Time to Cooling
Fiscal Year (will not be averaged, but will be part of the group by)
Referring Hospital State (will not be averaged, but will be part of the
group by)

I would like to use the following calculation that references Query 1 and
basically puts a specific year value for whichever value is true:

Fiscal Year: IIf([TimingCalc]![FY2006]=1,"FY2006",Null) Or
IIf([TimingCalc]![FY2007]=1,"FY2007",Null) Or
IIf([TimingCalc]![FY2008]=1,"FY2008",Null)

Unfortunately, it does not work. It will only output a -1 ("true") value for
each of these. It could be that I shouldn't be using an IIf statement, but I
can't think of what would work better. I can't group the query by fiscal year
if I can't get the darn calculation to work. Anyone have any suggestions
(besides creating a seperate table for this - which there are too many DOB
values to manually type in seperate Fiscal years that correspond in a
seperate table)?
 
Thank you very much for your help. I realized what you said about the 1 or 0
value for the range of date.
I ended up trying a different variation within the IIF statement based on
what you said to create accurate Fiscal Year values that are automated and
are based only on the DOB:

Fiscal Year: IIf(Month([dob])>=7 And
Month([dob])<=12,Year([DOB])+1,Year([DOB])))


And for Fiscal Quarter I did this:
Fiscal Quarter: IIf(Month([dob])>=7 And
Month([dob])<=9,"Q1",IIf(Month([dob])>=10 And
Month([dob])<=12,"Q2",IIf(Month([dob])>=1 And
Month([dob])<=3,"Q3",IIf(Month([dob])>=4 And Month([dob])<=6,"Q4"))))
(just in case anyone wanted a better way than what I had originally done
based on the IIF statement).

I figure this way I don't have to do any real work later when I have to
print another copy of the report for another quarter or fiscal year.

Thank you again for putting me on the right track.

Cheers!

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use this type of IIf() function:

Fiscal Year: IIf([TimingCalc].[FY2006]=1,"FY2006",
IIf([TimingCalc].[FY2007]=1,"FY2007",
IIf([TimingCalc].[FY2008]=1,"FY2008",
Null)))

This translates to an If...Then statement like this:

If TimingCalc.FY2006=1 Then
"FY2006"
ElseIf TimingCalc.FY2007=1 Then
"FY2007"
ElseIf TimingCalc.FY2008=1 Then
"FY2008"
Else
Null
End If

I'm assuming that "TimingCalc" is a table or query name.

BTW, the statement

Abs(([DOB]>#6/30/2005#) And ([DOB]<#7/1/2006#)))

is NOT an IIf() function it is a comparison that returns 1 or 0, which
Access interprets as True or False, respectively.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTX86IechKqOuFEgEQJwVACgoQnD0FflEdU8gSzkBtowyjNcwGUAnigU
NQBvpBunXHU7hUTKVr4WQ6l6
=NSoH
-----END PGP SIGNATURE-----

I am trying to create a query (query 2) that I will later use in a report. I
have 2 queries that are for used in my question. The idea is that I want to
group values by both State and by Year (in the end in a report) and average
the values that fall under a specific state and year. The averaging is of
course easy, but the grouping is not.

The 1st st query has a number of datediff and year calculations that will be
used in a number of other queries (i.e. Time to Arrival, FY2006, etc.), but
for the purpose of this question it will be used just for the 2nd query. The
1st query has the following calculation headings that will be referenced in
the 2nd query:
Time to Arrival (hrs) (Date diff function)
Time to Cooling (hrs) (Date diff function)
Age when MRI Done (days) (Date diff function)
FY2006 (which is an IIF statement that displays 1 if DOB is within the range
of the fiscal year, i.e. FY2006: Abs(([DOB]>#6/30/2005#) And
([DOB]<#7/1/2006#)) )
FY2007 (Similar IIF statement as above, but different range of dates)
FY2008 (Similar IIF statement as above, but different range of dates)
Current Qtr 1 (Similar IIF statement as above, but different range of dates)
Total (Similar IIF statement as above, but total range of dates)

Query 2 has a number of headings that have values that will later be
averaged and should be "grouped by" year and "Hospital State".
Time to Arrival
Time to Cooling
Fiscal Year (will not be averaged, but will be part of the group by)
Referring Hospital State (will not be averaged, but will be part of the
group by)

I would like to use the following calculation that references Query 1 and
basically puts a specific year value for whichever value is true:

Fiscal Year: IIf([TimingCalc]![FY2006]=1,"FY2006",Null) Or
IIf([TimingCalc]![FY2007]=1,"FY2007",Null) Or
IIf([TimingCalc]![FY2008]=1,"FY2008",Null)

Unfortunately, it does not work. It will only output a -1 ("true") value for
each of these. It could be that I shouldn't be using an IIf statement, but I
can't think of what would work better. I can't group the query by fiscal year
if I can't get the darn calculation to work. Anyone have any suggestions
(besides creating a seperate table for this - which there are too many DOB
values to manually type in seperate Fiscal years that correspond in a
seperate table)?
Click to expand...
Click to expand...
 
Back
Top