Nested If in Access Query or Report

  • Thread starter Thread starter taylorew
  • Start date Start date
T

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
 
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)
 
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])
Click to expand...

-----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-----
 
-----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
.
Click to expand...
 
-----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])
Click to expand...
Click to expand...

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")
 
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)
John Vinson said:
-----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])
Click to expand...
Click to expand...

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")
Click to expand...
 
Ummm - I already recommended that, but I guess you both missed the post. I
can see it on msnews.microsoft.com news server.
Click to expand...

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!
 
I suspected as much. Not a problem! Better to "echo" the answer than to
not answer at all!!
 
Back
Top