IIF statement

[Guest]

I want to create a new field (DBE/SBE Goal) in a query based on information
in existing fields (DBE % & SBE %).

How do I write the expression to get the following result: If there is a
figure in the DBE % field, give me that figure followed by "% DBE" and/or if
there is a figure in the SBE% field, give me that figure followed by "% SBE".
If both fields are empty, give me "None set".

Please advise.

 

[Douglas J. Steele]

What exactly do you want if both fields have values? Assuming you want both
values (comma-separated below), try:

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE), [SBE %] & " % SBE", "None set')

 

[BruceM]

Just as a thought, it is considered good practice to use only alphanumeric
characters and underscores in field names. Percent marks and the like have
other meanings in VBA. You will probably be OK if you sue brackets around
the field names, but why make it more difficult than it needs to be?

 

[Guest]

I agree but the tables were created before I came on here and the creators
aren't open to changing certain things - this is one of them.

 

[Guest]

If both fields have values, then both can be printed as you advise. However,
I tried the expression and got an error message:an expression you entered has
a function containing the wrong number of arguments.

Can you tell me what the LEN function does? Also, why the first and second
set of quotation marks in your recommendation? I get the logic behind the
rest of it though.
--
Thanks, Karen

What exactly do you want if both fields have values? Assuming you want both
values (comma-separated below), try:

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE), [SBE %] & " % SBE", "None set')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want to create a new field (DBE/SBE Goal) in a query based on information
in existing fields (DBE % & SBE %).

How do I write the expression to get the following result: If there is a
figure in the DBE % field, give me that figure followed by "% DBE" and/or
if
there is a figure in the SBE% field, give me that figure followed by "%
SBE".
If both fields are empty, give me "None set".

Please advise.

 

[Douglas J. Steele]

Sorry, my fault. Slight typo (forgot a closing quote after the second %DBE):

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE"), [SBE %] & " % SBE", "None set')


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If both fields have values, then both can be printed as you advise.
However,
I tried the expression and got an error message:an expression you entered
has
a function containing the wrong number of arguments.

Can you tell me what the LEN function does? Also, why the first and
second
set of quotation marks in your recommendation? I get the logic behind the
rest of it though.
--
Thanks, Karen

What exactly do you want if both fields have values? Assuming you want
both
values (comma-separated below), try:

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE), [SBE %] & " % SBE", "None set')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want to create a new field (DBE/SBE Goal) in a query based on
information
in existing fields (DBE % & SBE %).

How do I write the expression to get the following result: If there is
a
figure in the DBE % field, give me that figure followed by "% DBE"
and/or
if
there is a figure in the SBE% field, give me that figure followed by "%
SBE".
If both fields are empty, give me "None set".

Please advise.

 

[Guest]

Thanks Douglas. Again, I tried it and got the same error message.
--
Thanks, Karen

Sorry, my fault. Slight typo (forgot a closing quote after the second %DBE):

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE"), [SBE %] & " % SBE", "None set')


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If both fields have values, then both can be printed as you advise.
However,
I tried the expression and got an error message:an expression you entered
has
a function containing the wrong number of arguments.

Can you tell me what the LEN function does? Also, why the first and
second
set of quotation marks in your recommendation? I get the logic behind the
rest of it though.
--
Thanks, Karen

What exactly do you want if both fields have values? Assuming you want
both
values (comma-separated below), try:

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE), [SBE %] & " % SBE", "None set')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I want to create a new field (DBE/SBE Goal) in a query based on
information
in existing fields (DBE % & SBE %).

How do I write the expression to get the following result: If there is
a
figure in the DBE % field, give me that figure followed by "% DBE"
and/or
if
there is a figure in the SBE% field, give me that figure followed by "%
SBE".
If both fields are empty, give me "None set".

Please advise.

 

[Guest]

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE"),IIf(Len([SBE %] & "")>0,[SBE %] & " %
SBE", "None set'))

Try this. Appears to have had 3 conditions set for on iif.

Thanks Douglas. Again, I tried it and got the same error message.
--
Thanks, Karen

Sorry, my fault. Slight typo (forgot a closing quote after the second %DBE):

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE"), [SBE %] & " % SBE", "None set')


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If both fields have values, then both can be printed as you advise.
However,
I tried the expression and got an error message:an expression you entered
has
a function containing the wrong number of arguments.

Can you tell me what the LEN function does? Also, why the first and
second
set of quotation marks in your recommendation? I get the logic behind the
rest of it though.
--
Thanks, Karen


:

What exactly do you want if both fields have values? Assuming you want
both
values (comma-separated below), try:

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE), [SBE %] & " % SBE", "None set')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I want to create a new field (DBE/SBE Goal) in a query based on
information
in existing fields (DBE % & SBE %).

How do I write the expression to get the following result: If there is
a
figure in the DBE % field, give me that figure followed by "% DBE"
and/or
if
there is a figure in the SBE% field, give me that figure followed by "%
SBE".
If both fields are empty, give me "None set".

Please advise.

 

[Douglas J. Steele]

Sorry. Not my day!

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE"), IIf(Len([SBE %] & "")>0,[SBE %] & "
% SBE", "None set"))

I tend to use Len([Field] & "") > 0 rather than Not IsNull([Field]). This is
because it will catch both Null fields and zero-length string fields ("").
All it's doing is append a ZLS to the field, and then seeing if its length
is 0 or not. You need to add the ZLS because Len doesn't work on Null
fields. In actual fact, if your fields are numeric (as I now have a sneaky
feeling they might be), you may as well use:

IIf(IsNull([DBE %]), IIf(IsNull([SBE %]), "None set", [SBE %] & " % SBE"),
IIf(IsNull([SBE %]), [DBE %] & " %DBE", [DBE %] & " %DBE, " & [SBE %] & " %
SBE"))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas. Again, I tried it and got the same error message.
--
Thanks, Karen

Sorry, my fault. Slight typo (forgot a closing quote after the second
%DBE):

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE, " &
[SBE %] & " % SBE", [DBE %] & " %DBE"), [SBE %] & " % SBE", "None set')


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If both fields have values, then both can be printed as you advise.
However,
I tried the expression and got an error message:an expression you
entered
has
a function containing the wrong number of arguments.

Can you tell me what the LEN function does? Also, why the first and
second
set of quotation marks in your recommendation? I get the logic behind
the
rest of it though.
--
Thanks, Karen


:

What exactly do you want if both fields have values? Assuming you want
both
values (comma-separated below), try:

IIf(Len([DBE %] & "") > 0, IIf(Len([SBE %] & "")>0, [DBE %] & " %DBE,
" &
[SBE %] & " % SBE", [DBE %] & " %DBE), [SBE %] & " % SBE", "None set')

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I want to create a new field (DBE/SBE Goal) in a query based on
information
in existing fields (DBE % & SBE %).

How do I write the expression to get the following result: If there
is
a
figure in the DBE % field, give me that figure followed by "% DBE"
and/or
if
there is a figure in the SBE% field, give me that figure followed by
"%
SBE".
If both fields are empty, give me "None set".

Please advise.