Complex If statement - please help!

[Guest]

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!

 

[Douglas J. Steele]

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])

 

[AlCamp]

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

 

[Duane Hookom]

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

 

[Guest]

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

 

[Guest]

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 <i>NEVER</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

 

[Duane Hookom]

I tested my expression with the first field being null.

 

[Guest]

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

 

[AlCamp]

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 <i>NEVER</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

 

[AlCamp]

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

 

[Duane Hookom]

I would just take the existing solution and play with it. A Null+String =
Null. Null & String = String.

I am beginning to question your table structure if you are going beyond 3
fields. Are you sure your table is normalized?

 

[Guest]

myStr = IIF(IsNull(Field1),"", Field1 & IIF(IsNull(Field2), "", ", " &
Field2) & IIF(IsNull(Field3), "", ", " & Field3)

==> I'd end up with ", Field1" then if Field1 is not NULL. And I don't want
a leading comma. And I believe it's the same problem for Doug's solution:
(", " + [Field1]) & (", " + [Field2]) & (", " + [Field3])

 

[Guest]

Thanks for the explanation.
Here's the structure of my table. I link it with an Excel sheet. It's set
up like this:

Item# Customer A Customer B
Customer C
===============================================
11-00011 A1000 B-Whatever1 NULL
11-00012 NULL B-Whatever2 NULL
11-00013 NULL NULL
C-20ABF
11-00014 NULL NULL
NULL

As you can tell, this is a Cross Reference List. We have our list of item
numbers that reference the same numbers as some of our customers. Our
customers might call these items under their own unique names, but
essentially they're all the same thing. Well, now I need to print some
labels that have our item numbers and a sequential list of corresponding
customer numbers following it (The label is quite small so I can't afford
listing the customer numbers line by line). So basically I'd like to see
this:
11-00011: A1000, B-Whatever1
11-00012: B-Whatever2
11-00013: C-20ABF
11-00014:

What would you recommend me to do?
Thanks so much!
ngan

 

[ngan]

Are you really another "Ngan"? Because that is strange to
find someone else having the same name.

Ngan

 

[Guest]

Yup

 

[Duane Hookom]

Normalize to fields like:
ItemNum CustomerID CustItemNum
======= ========== ==========
11-00011 CustA A1000
11-00011 CustB B-Whatever1

It's your decision since this is a linked Excel sheet but I wouldn't spend
any time creating work-arounds for un-normalized tables.