Problems with IIf statement

[Chris]

Hi!

I'm having issues using the IIf statement a control source
in an unbound text box on my report. Aside from this, my
report works exactly the way I wanted. I hope someone can
help.

My statement is below:

IIf([Storage Location]="Mfg","Manufacturing",IIf([Storage
Location]="Prod Eng","Product Engineering",IIf([Storage
Location]="Cust Svc","Customer Service","R & D")))

"Storage Location" is a text field in the underlying
tables upon which my queries are based. My report is
grouped on this field and I would like to see the full
department name rather than the abbreviation. I had
intended to hide the control for "Storage Location" and
display the unbound text box that had the above statement
as its control source. However, Access keeps giving me
the following message:

"Syntax error in query expression 'First([IIf([Storage
Location]="Mfg","Manufacturing",IIf([Storage Location]
="Prod Eng","Product Engineering",IIf([Storage Location]
="Cust Svc","Customer Service","R & D")))])"

Choosing Help instead of OK at the error prompt only gives
me a grey screen so I can't even get help from Access!
(Help normally works okay).

I can't figure out why the error message puts the 'First
([...]) around my statement and I can't figure out what
I'm missing. I've tried quotes versus no quotes, spaces
versus no spaces, and a few other things and I just can't
see it.

Thanks for any and all help!

 

[Brian Camire]

For starters, you might try using the Switch function instead. It might
look something like this:

Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",True,"R & D")

The problem with your original statement was that the IIf function takes
three arguments, and in all but the innermost call you were only providing
two.

The Switch statement I suggested will return "R & D" if [Storage Location]
is not "Mfg", "Prod Eng", or "Cust Svc". This might work for now, but what
happens if you add a new [Storage Location] (such as "Whs" for
"Wharehouse")? For starters, you might want to modify the statement to
check for the [Storage Location] that corresponds to "R & D". Assuming it's
"R&D", you might try:

Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",[Storage Location]="R&D","R & D")

The statement will return Null if [Storage Location] is not "Mfg", "Prod
Eng", "Cust Svc", or "R&D". This will show up as a blank on your report and
at least alert you to the fact that you're missing something.

In the long run, you might think about adding a new table (say, named
"Storage Locations") that gives the name of each [Storage Location]. It
might look something like this:

Storage Location, Storage Location Name
Mfg, Manufacturing
Prod Eng, Product Engineering
Cust Svc, Customer Service
R&D, R & D

In the report you're query is based on, you could join this table to the
table you're getting the [Storage Location] field from now, and then have a
control on your report bound to the [Storage Location Name] field. You
would no longer need the unbound control or its Switch (or IIf) statement,
and you wouldn't need to change your report if you added, deleted or changed
a [Storage Location] -- you would just make the changes in the "Storage
Locations" table.

Hope this helps.

Hi!

I'm having issues using the IIf statement a control source
in an unbound text box on my report. Aside from this, my
report works exactly the way I wanted. I hope someone can
help.

My statement is below:

IIf([Storage Location]="Mfg","Manufacturing",IIf([Storage
Location]="Prod Eng","Product Engineering",IIf([Storage
Location]="Cust Svc","Customer Service","R & D")))

"Storage Location" is a text field in the underlying
tables upon which my queries are based. My report is
grouped on this field and I would like to see the full
department name rather than the abbreviation. I had
intended to hide the control for "Storage Location" and
display the unbound text box that had the above statement
as its control source. However, Access keeps giving me
the following message:

"Syntax error in query expression 'First([IIf([Storage
Location]="Mfg","Manufacturing",IIf([Storage Location]
="Prod Eng","Product Engineering",IIf([Storage Location]
="Cust Svc","Customer Service","R & D")))])"

Choosing Help instead of OK at the error prompt only gives
me a grey screen so I can't even get help from Access!
(Help normally works okay).

I can't figure out why the error message puts the 'First
([...]) around my statement and I can't figure out what
I'm missing. I've tried quotes versus no quotes, spaces
versus no spaces, and a few other things and I just can't
see it.

Thanks for any and all help!

 

[Chris]

Hi Brian,

Thanks for your prompt reply!

I cut and pasted your switch statement into the control
source to avoid typing errors and I got exactly the same
error as when I used the IIf statment.

Argh!

-----Original Message-----
For starters, you might try using the Switch function instead. It might
look something like this:

Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",True,"R & D")

The problem with your original statement was that the IIf function takes
three arguments, and in all but the innermost call you were only providing
two.

The Switch statement I suggested will return "R & D" if [Storage Location]
is not "Mfg", "Prod Eng", or "Cust Svc". This might work for now, but what
happens if you add a new [Storage Location] (such as "Whs" for
"Wharehouse")? For starters, you might want to modify the statement to
check for the [Storage Location] that corresponds to "R & D". Assuming it's
"R&D", you might try:

Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",[Storage Location] ="R&D","R & D")

The statement will return Null if [Storage Location] is not "Mfg", "Prod
Eng", "Cust Svc", or "R&D". This will show up as a blank on your report and
at least alert you to the fact that you're missing something.

In the long run, you might think about adding a new table (say, named
"Storage Locations") that gives the name of each [Storage Location]. It
might look something like this:

Storage Location, Storage Location Name
Mfg, Manufacturing
Prod Eng, Product Engineering
Cust Svc, Customer Service
R&D, R & D

In the report you're query is based on, you could join this table to the
table you're getting the [Storage Location] field from now, and then have a
control on your report bound to the [Storage Location Name] field. You
would no longer need the unbound control or its Switch (or IIf) statement,
and you wouldn't need to change your report if you added, deleted or changed
a [Storage Location] -- you would just make the changes in the "Storage
Locations" table.

Hope this helps.

Hi!

I'm having issues using the IIf statement a control source
in an unbound text box on my report. Aside from this, my
report works exactly the way I wanted. I hope someone can
help.

My statement is below:

IIf([Storage Location]="Mfg","Manufacturing",IIf ([Storage
Location]="Prod Eng","Product Engineering",IIf([Storage
Location]="Cust Svc","Customer Service","R & D")))

"Storage Location" is a text field in the underlying
tables upon which my queries are based. My report is
grouped on this field and I would like to see the full
department name rather than the abbreviation. I had
intended to hide the control for "Storage Location" and
display the unbound text box that had the above statement
as its control source. However, Access keeps giving me
the following message:

"Syntax error in query expression 'First([IIf([Storage
Location]="Mfg","Manufacturing",IIf([Storage Location]
="Prod Eng","Product Engineering",IIf([Storage Location]
="Cust Svc","Customer Service","R & D")))])"

Choosing Help instead of OK at the error prompt only gives
me a grey screen so I can't even get help from Access!
(Help normally works okay).

I can't figure out why the error message puts the 'First
([...]) around my statement and I can't figure out what
I'm missing. I've tried quotes versus no quotes, spaces
versus no spaces, and a few other things and I just can't
see it.

Thanks for any and all help!

.

 

[Brian Camire]

If you're using an expression in the Control Source property of a control,
add an equals sign at the beginning, as in:

=Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage Location]="Cust Svc","Customer
Service",True,"R & D")

If you are copying and pasting the expression from the newsgroup message,
remove any hard line breaks that are carried over.

Hi Brian,

Thanks for your prompt reply!

I cut and pasted your switch statement into the control
source to avoid typing errors and I got exactly the same
error as when I used the IIf statment.

Argh!

-----Original Message-----
For starters, you might try using the Switch function instead. It might
look something like this:

Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",True,"R & D")

The problem with your original statement was that the IIf function takes
three arguments, and in all but the innermost call you were only providing
two.

The Switch statement I suggested will return "R & D" if [Storage Location]
is not "Mfg", "Prod Eng", or "Cust Svc". This might work for now, but what
happens if you add a new [Storage Location] (such as "Whs" for
"Wharehouse")? For starters, you might want to modify the statement to
check for the [Storage Location] that corresponds to "R & D". Assuming it's
"R&D", you might try:

Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",[Storage Location] ="R&D","R & D")

The statement will return Null if [Storage Location] is not "Mfg", "Prod
Eng", "Cust Svc", or "R&D". This will show up as a blank on your report and
at least alert you to the fact that you're missing something.

In the long run, you might think about adding a new table (say, named
"Storage Locations") that gives the name of each [Storage Location]. It
might look something like this:

Storage Location, Storage Location Name
Mfg, Manufacturing
Prod Eng, Product Engineering
Cust Svc, Customer Service
R&D, R & D

In the report you're query is based on, you could join this table to the
table you're getting the [Storage Location] field from now, and then have a
control on your report bound to the [Storage Location Name] field. You
would no longer need the unbound control or its Switch (or IIf) statement,
and you wouldn't need to change your report if you added, deleted or changed
a [Storage Location] -- you would just make the changes in the "Storage
Locations" table.

Hope this helps.

Hi!

I'm having issues using the IIf statement a control source
in an unbound text box on my report. Aside from this, my
report works exactly the way I wanted. I hope someone can
help.

My statement is below:

IIf([Storage Location]="Mfg","Manufacturing",IIf ([Storage
Location]="Prod Eng","Product Engineering",IIf([Storage
Location]="Cust Svc","Customer Service","R & D")))

"Storage Location" is a text field in the underlying
tables upon which my queries are based. My report is
grouped on this field and I would like to see the full
department name rather than the abbreviation. I had
intended to hide the control for "Storage Location" and
display the unbound text box that had the above statement
as its control source. However, Access keeps giving me
the following message:

"Syntax error in query expression 'First([IIf([Storage
Location]="Mfg","Manufacturing",IIf([Storage Location]
="Prod Eng","Product Engineering",IIf([Storage Location]
="Cust Svc","Customer Service","R & D")))])"

Choosing Help instead of OK at the error prompt only gives
me a grey screen so I can't even get help from Access!
(Help normally works okay).

I can't figure out why the error message puts the 'First
([...]) around my statement and I can't figure out what
I'm missing. I've tried quotes versus no quotes, spaces
versus no spaces, and a few other things and I just can't
see it.

Thanks for any and all help!

.

 

[Chris]

Thanks!!! The equal sign did it!

Now, both the IIf statement and the Switch statement work
equally well.

Just a comment about your earlier statement "The problem
with your original statement was that the IIf function
takes three arguments, and in all but the innermost call
you were only providing two.".... each nested IIf was my
third arguement - was it not?

Thanks again! I really appreciate your help!

-----Original Message-----
If you're using an expression in the Control Source property of a control,
add an equals sign at the beginning, as in:

=Switch([Storage Location]="Mfg","Manufacturing",[Storage Location]="Prod
Eng","Product Engineering",[Storage Location]="Cust Svc","Customer
Service",True,"R & D")

If you are copying and pasting the expression from the newsgroup message,
remove any hard line breaks that are carried over.

Hi Brian,

Thanks for your prompt reply!

I cut and pasted your switch statement into the control
source to avoid typing errors and I got exactly the same
error as when I used the IIf statment.

Argh!

-----Original Message-----
For starters, you might try using the Switch function instead. It might
look something like this:

Switch([Storage Location]="Mfg","Manufacturing",

[Storage
Location]="Prod

Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",True,"R & D")

The problem with your original statement was that the

IIf
function takes

three arguments, and in all but the innermost call you were only providing
two.

The Switch statement I suggested will return "R & D" if [Storage Location]
is not "Mfg", "Prod Eng", or "Cust Svc". This might

work
for now, but what

happens if you add a new [Storage Location] (such as "Whs" for
"Wharehouse")? For starters, you might want to modify the statement to
check for the [Storage Location] that corresponds

to "R &
D". Assuming it's

"R&D", you might try:

Switch([Storage Location]="Mfg","Manufacturing",

[Storage
Location]="Prod

Eng","Product Engineering",[Storage
Location]="Cust Svc","Customer Service",[Storage

Location]
="R&D","R & D")

The statement will return Null if [Storage Location] is not "Mfg", "Prod
Eng", "Cust Svc", or "R&D". This will show up as a

blank
on your report and

at least alert you to the fact that you're missing something.

In the long run, you might think about adding a new

table
(say, named

"Storage Locations") that gives the name of each

[Storage
Location]. It

might look something like this:

Storage Location, Storage Location Name
Mfg, Manufacturing
Prod Eng, Product Engineering
Cust Svc, Customer Service
R&D, R & D

In the report you're query is based on, you could join this table to the
table you're getting the [Storage Location] field from now, and then have a
control on your report bound to the [Storage Location Name] field. You
would no longer need the unbound control or its Switch (or IIf) statement,
and you wouldn't need to change your report if you

added,
deleted or changed

a [Storage Location] -- you would just make the changes in the "Storage
Locations" table.

Hope this helps.

Hi!

I'm having issues using the IIf statement a control source
in an unbound text box on my report. Aside from

this,
my

report works exactly the way I wanted. I hope

someone
can

help.

My statement is below:

IIf([Storage Location]="Mfg","Manufacturing",IIf ([Storage
Location]="Prod Eng","Product Engineering",IIf ([Storage
Location]="Cust Svc","Customer Service","R & D")))

"Storage Location" is a text field in the underlying
tables upon which my queries are based. My report is
grouped on this field and I would like to see the full
department name rather than the abbreviation. I had
intended to hide the control for "Storage Location" and
display the unbound text box that had the above statement
as its control source. However, Access keeps giving me
the following message:

"Syntax error in query expression 'First([IIf ([Storage
Location]="Mfg","Manufacturing",IIf([Storage Location]
="Prod Eng","Product Engineering",IIf([Storage Location]
="Cust Svc","Customer Service","R & D")))])"

Choosing Help instead of OK at the error prompt only gives
me a grey screen so I can't even get help from Access!
(Help normally works okay).

I can't figure out why the error message puts the 'First
([...]) around my statement and I can't figure out what
I'm missing. I've tried quotes versus no quotes, spaces
versus no spaces, and a few other things and I just can't
see it.

Thanks for any and all help!


.

.