Complex If statement - please help!

Hi,
I have a report based on a table that is comprised of Field1, Field2, and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, , Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the ControlSource of
the Textbox?????
Thanks very much for any suggestions!

Actually, you don't need to use Ifs at all. You can take advantage of the
fact that + as a concatenation character works differently with Nulls than &
does.

Try [Field1] & (", " + [Field2]) & (", " + [Field3])

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Ngan" wrote in message
...
Hi,
I have a report based on a table that is comprised of Field1, Field2, and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, , Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the ControlSource
of
the Textbox?????
Thanks very much for any suggestions!

NGan,
Try this...
myStr = Field1 & IIF(IsNull(Field2), "", ", " & Field2) &
IIF(IsNull(Field3), "", ", " & Field3)

Didn't have time to test this, but it should be OK. This should yield
"Field1, Field3" or "Field3" etc.. for any combination of values.

hth
Al Camp

"Ngan" wrote in message
...
Hi,
I have a report based on a table that is comprised of Field1, Field2, and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, , Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the ControlSource
of
the Textbox?????
Thanks very much for any suggestions!

The following should work but might not be the best
=[Field1] & IIf(IsNull([Field1]),"",",")+[Field2] & IIf(IsNull([field2]) And
IsNull([field1]),"",",")+[Field3]

--
Duane Hookom
MS Access MVP


"Ngan" wrote in message
...
Hi,
I have a report based on a table that is comprised of Field1, Field2, and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, , Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the ControlSource
of
the Textbox?????
Thanks very much for any suggestions!

And ya'll just make me feel so dumb! Thanks everyone for the wonderful ideas!
I just need to add the blank string checking part (since I can't guarantee
that it'll just be NULL) but thanks so much for helping me start!

Sincerely,
-ngan

Hi everyone again,
This isn't quite working yet due to the following problem:
I think I misled everyone to think that my Field1 is iNEVER/i NULL.
Problem is it can be null as well. So if Field1 is NULL and Field2 is not,
I'll get ", Field2" string based on your suggestions.

Hmm... how do I make it understand that I would like a leading comma only if
I'm the 2nd guy in the row? If I'm the first guy, please do not add comma.

If I using a trailing comma instead of leading, then I'd have the reverse
problem where I'd get a comma hanging off at the end, like "Field1, Field2, "

I know this seems to be too minor to consume everyone's effort on it, but it
is really bugging me!!! (*Sigh*)

Thanks very much again!
-ngan

"Ngan" wrote:

Hi,
I have a report based on a table that is comprised of Field1, Field2, and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, , Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the ControlSource of
the Textbox?????
Thanks very much for any suggestions!

I tested my expression with the first field being null.

--
Duane Hookom
MS Access MVP


"Ngan" wrote in message
...
Hi everyone again,
This isn't quite working yet due to the following problem:
I think I misled everyone to think that my Field1 is iNEVER/i NULL.
Problem is it can be null as well. So if Field1 is NULL and Field2 is
not,
I'll get ", Field2" string based on your suggestions.

Hmm... how do I make it understand that I would like a leading comma only
if
I'm the 2nd guy in the row? If I'm the first guy, please do not add
comma.

If I using a trailing comma instead of leading, then I'd have the reverse
problem where I'd get a comma hanging off at the end, like "Field1,
Field2, "

I know this seems to be too minor to consume everyone's effort on it, but
it
is really bugging me!!! (*Sigh*)

Thanks very much again!
-ngan

"Ngan" wrote:

Hi,
I have a report based on a table that is comprised of Field1, Field2,
and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank
as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, ,
Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the
ControlSource of
the Textbox?????
Thanks very much for any suggestions!

I don't exactly know what the difference between + and & is, but seems like a
phrase with + will be gone if 1 of the arguments is Null, right?
Yours does work but I don't know how to extend it with more than 3 fields.
Can you get me a general rule on this? Let's say I have 10 fields and would
like to accomplish the same result. I've tried to play with it but my brain
is completely shut down now.
Thank you again for your time!
-ngan

"Duane Hookom" wrote:

I tested my expression with the first field being null.

--
Duane Hookom
MS Access MVP


"Ngan" wrote in message
...
Hi everyone again,
This isn't quite working yet due to the following problem:
I think I misled everyone to think that my Field1 is iNEVER/i NULL.
Problem is it can be null as well. So if Field1 is NULL and Field2 is
not,
I'll get ", Field2" string based on your suggestions.

Hmm... how do I make it understand that I would like a leading comma only
if
I'm the 2nd guy in the row? If I'm the first guy, please do not add
comma.

If I using a trailing comma instead of leading, then I'd have the reverse
problem where I'd get a comma hanging off at the end, like "Field1,
Field2, "

I know this seems to be too minor to consume everyone's effort on it, but
it
is really bugging me!!! (*Sigh*)

Thanks very much again!
-ngan

"Ngan" wrote:

Hi,
I have a report based on a table that is comprised of Field1, Field2,
and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank
as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, ,
Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the
ControlSource of
the Textbox?????
Thanks very much for any suggestions!

"Ngan" wrote in message
...
Hi everyone again,
This isn't quite working yet due to the following problem:
I think I misled everyone to think that my Field1 is iNEVER/i NULL.
Problem is it can be null as well. So if Field1 is NULL and Field2 is
not,
I'll get ", Field2" string based on your suggestions.

Hmm... how do I make it understand that I would like a leading comma only
if
I'm the 2nd guy in the row? If I'm the first guy, please do not add
comma.

If I using a trailing comma instead of leading, then I'd have the reverse
problem where I'd get a comma hanging off at the end, like "Field1,
Field2, "

I know this seems to be too minor to consume everyone's effort on it, but
it
is really bugging me!!! (*Sigh*)

Thanks very much again!
-ngan

"Ngan" wrote:

Hi,
I have a report based on a table that is comprised of Field1, Field2,
and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank
as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, ,
Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the
ControlSource of
the Textbox?????
Thanks very much for any suggestions!

My original reworked for Field1 being Null (all one line)
myStr = IIF(IsNull(Field1),"", Field1 & IIF(IsNull(Field2), "", ", "
& Field2) &
IIF(IsNull(Field3), "", ", " & Field3)

Or... Doug Steele's solution is pretty neat (I added the Field1 handling to
his solution)
(", " + [Field1]) & (", " + [Field2]) & (", " + [Field3])
hth
Al Camp


"Ngan" wrote in message
...
Hi everyone again,
This isn't quite working yet due to the following problem:
I think I misled everyone to think that my Field1 is iNEVER/i NULL.
Problem is it can be null as well. So if Field1 is NULL and Field2 is
not,
I'll get ", Field2" string based on your suggestions.

Hmm... how do I make it understand that I would like a leading comma only
if
I'm the 2nd guy in the row? If I'm the first guy, please do not add
comma.

If I using a trailing comma instead of leading, then I'd have the reverse
problem where I'd get a comma hanging off at the end, like "Field1,
Field2, "

I know this seems to be too minor to consume everyone's effort on it, but
it
is really bugging me!!! (*Sigh*)

Thanks very much again!
-ngan

"Ngan" wrote:

Hi,
I have a report based on a table that is comprised of Field1, Field2,
and
Field3
I'd like to have textbox that will display "Field1, Field2, Field3" (as
quoted). Any of those 3 fields can be blank (all of them can be blank
as
well) and the textbox needs to adjust itself appropriately. For EX, if
Field2 is blank, it should say "Field1, Field3" and not "Field1, ,
Field3"

A simple IF statement in VBA would be:

dim myStr as String
myStr = Field1
If Field2 "" Then
myStr = "," & Field2
End If
If Field3 "" Then
myStr = "," & Field3
End If

But how do I convert this to an IIF statement to put in the ControlSourc
e of
the Textbox?????
Thanks very much for any suggestions!