Nested If in Access Query or Report

[taylorew]

I am having trouble creating a query I need to run a
report. The problem is in how I am setting up my "IF"
statement. I am submitting this incorrect "IF" statement
hoping someone can help me repair it. I need to put the
following expression in a query or in the report itself.
The input field I am examining is called
"LengthCurrentTerm" and based on the contents of the field
I want the name of a month returned. Please advise:

Select(IIf([LengthCurrentTerm]="1 yr"),"February",
Elseif([LengthCurrentTerm]="2 yrs"),"December",
Elseif([LengthCurrentTerm]="3 yrs"),"June",
Elseif([LengthCurrentTerm]="4 yrs"),"June",
Elseif([LengthCurrentTerm]="5 yrs"),"June"AS [Month])

Thank you,

Ella Taylor

 

[John Viescas]

Ella-

You cannot use ElseIF in the IIF function. I suspect that the Switch
function will work better:

SELECT Switch([LengthCurrentTerm]="1 yr","February", [LengthCurrentTerm]="2
yrs", "December",
[LengthCurrentTerm]="3 yrs", "June", [LengthCurrentTerm]="4 yrs", "June",
[LengthCurrentTerm]="5 yrs", "June") AS [Month]
FROM ...


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[MGFoster]

I am having trouble creating a query I need to run a
report. The problem is in how I am setting up my "IF"
statement. I am submitting this incorrect "IF" statement
hoping someone can help me repair it. I need to put the
following expression in a query or in the report itself.
The input field I am examining is called
"LengthCurrentTerm" and based on the contents of the field
I want the name of a month returned. Please advise:

Select(IIf([LengthCurrentTerm]="1 yr"),"February",
Elseif([LengthCurrentTerm]="2 yrs"),"December",
Elseif([LengthCurrentTerm]="3 yrs"),"June",
Elseif([LengthCurrentTerm]="4 yrs"),"June",
Elseif([LengthCurrentTerm]="5 yrs"),"June"AS [Month])

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

It's easier to use the Switch() function in this case.

Switch(LengthCurrentTery="1 yr", "February",
LengthCurrentTery="2 yrs", "December",
LengthCurrentTery="3 yrs", "June") As [Month]

Add more selections as required.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQFIrQYechKqOuFEgEQLohQCfZCYI4bcFrR57R6P4QV/dqiuIa54AoPDJ
xdTPkYPRCFi16B3BvrRlsd6x
=0HDL
-----END PGP SIGNATURE-----

 

[Ella]

-----Original Message-----
I am having trouble creating a query I need to run a
report. The problem is in how I am setting up my "IF"
statement. I am submitting this incorrect "IF" statement
hoping someone can help me repair it. I need to put the
following expression in a query or in the report itself.
The input field I am examining is called
"LengthCurrentTerm" and based on the contents of the field
I want the name of a month returned. Please advise:

Select(IIf([LengthCurrentTerm]="1 yr"),"February",
Elseif([LengthCurrentTerm]="2 yrs"),"December",
Elseif([LengthCurrentTerm]="3 yrs"),"June",
Elseif([LengthCurrentTerm]="4 yrs"),"June",
Elseif([LengthCurrentTerm]="5 yrs"),"June"AS [Month])

Thank you,

Ella Taylor
.

 

[John Vinson]

-----Original Message-----
I am having trouble creating a query I need to run a
report. The problem is in how I am setting up my "IF"
statement. I am submitting this incorrect "IF" statement
hoping someone can help me repair it. I need to put the
following expression in a query or in the report itself.
The input field I am examining is called
"LengthCurrentTerm" and based on the contents of the field
I want the name of a month returned. Please advise:

Select(IIf([LengthCurrentTerm]="1 yr"),"February",
Elseif([LengthCurrentTerm]="2 yrs"),"December",
Elseif([LengthCurrentTerm]="3 yrs"),"June",
Elseif([LengthCurrentTerm]="4 yrs"),"June",
Elseif([LengthCurrentTerm]="5 yrs"),"June"AS [Month])

I'd suggest using the Switch() function instead:

SELECT Switch([LengthCurrentTerm]="1 yr"),"February",
[LengthCurrentTerm]="2 yrs","December",
[LengthCurrentTerm]="3 yrs","June",
[LengthCurrentTerm]="4 yrs","June",
[LengthCurrentTerm]="5 yrs","June",
True, "Erroneous LengthCurrentTerm")

 

[John Viescas]

Ummm - I already recommended that, but I guess you both missed the post. I
can see it on msnews.microsoft.com news server.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

-----Original Message-----
I am having trouble creating a query I need to run a
report. The problem is in how I am setting up my "IF"
statement. I am submitting this incorrect "IF" statement
hoping someone can help me repair it. I need to put the
following expression in a query or in the report itself.
The input field I am examining is called
"LengthCurrentTerm" and based on the contents of the field
I want the name of a month returned. Please advise:

Select(IIf([LengthCurrentTerm]="1 yr"),"February",
Elseif([LengthCurrentTerm]="2 yrs"),"December",
Elseif([LengthCurrentTerm]="3 yrs"),"June",
Elseif([LengthCurrentTerm]="4 yrs"),"June",
Elseif([LengthCurrentTerm]="5 yrs"),"June"AS [Month])

I'd suggest using the Switch() function instead:

SELECT Switch([LengthCurrentTerm]="1 yr"),"February",
[LengthCurrentTerm]="2 yrs","December",
[LengthCurrentTerm]="3 yrs","June",
[LengthCurrentTerm]="4 yrs","June",
[LengthCurrentTerm]="5 yrs","June",
True, "Erroneous LengthCurrentTerm")

 

[John Vinson]

Ummm - I already recommended that, but I guess you both missed the post. I
can see it on msnews.microsoft.com news server.

Hm. Didn't make it to my news server! I'm seeing quite a few posts
that Agent can't download, and a few that evidently are missing as I
can see replies but not the original post. Sorry for (unintentionally)
duplicating your reply!

 

[John Viescas]

I suspected as much. Not a problem! Better to "echo" the answer than to
not answer at all!!