combine fields

[rml]

I'm using the following code to combine four fields. It works fine. My
problem is when all the fields are blank I get #Error on the report for that
record. What I would like when all fields are blank is "NA" to be placed.

Thanks.

=Left(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] & ",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"),Len(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] & ",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"))-2)

 

[boblarson]

Look into using the NZ function. However, it looks like you have a
normalization problem which could be solved with some redesign. There really
shouldn't be repeating fields np1, np2, np3, etc. But, only you can decide
how you're going to go about it.
--
Bob Larson
Access World Forums Super Moderator

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[rml]

Someone from microsoft help me create this code originally. It may not be
perfect but it works. I just need to add if all the fields are empty then
"NA" and that would be fine. Can you show me an example?

Thanks.

 

[Pieter Wijnen]

=IIF(IsNull(np1+np2+np3+np4),"NA",Left(IIf(IsNull([np1]),"",[np1] & ", ") &
IIf(IsNull([np2]),"",[np2] & ", ") & IIf(IsNull([np3]),"",[np3] & ", ") &
IIf(IsNull([np4]),"",[np4] & ", "),Len(IIf(IsNull([np1]),"",[np1] & ", ") &
IIf(IsNull([np2]),""[np2] & ", ") & IIf(IsNull([np3]),"",[np3] & ", ") &
IIf(IsNull([np4]),"",[np4] & ", "))-2))

HtH

Pieter

Someone from microsoft help me create this code originally. It may not be
perfect but it works. I just need to add if all the fields are empty then
"NA" and that would be fine. Can you show me an example?

Thanks.

I'm using the following code to combine four fields. It works fine. My
problem is when all the fields are blank I get #Error on the report for
that
record. What I would like when all fields are blank is "NA" to be
placed.

Thanks.

=Left(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] &
",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"),Len(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] &
",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"))-2)

 

[Tom Wickerath]

Perhaps I'm not seeing something, but I think you can greatly simplify your
expression to the following:

=(np1 + ", ") & (np2 + ", ") & (np3 + ", ") & np4

Make sure that the name of the control is *not* the same as the name of any
of your fields, or else you will get a circular reference error. You're
apparently okay on this, since you indicated an error only if all four fields
are null. If you had a circular reference error, you'd have it all the time.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I'm using the following code to combine four fields. It works fine. My
problem is when all the fields are blank I get #Error on the report for that
record. What I would like when all fields are blank is "NA" to be placed.

Thanks.

=Left(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] & ",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"),Len(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] & ",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"))-2)

 

[Pieter Wijnen]

You got it once again ;-)
Pieter

 

[Tom Wickerath]

Hi Pieter,

Hopefully "RML" will agree that this works for him/her.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi RML.

I just realized that I did not address one of your original requirements: to
make it NA if all fields are null. Okay, so we will add the Nz function, as
Bob Larson suggested. Does this work for you?


=Nz(((np1 + ", ") & (np2 + ", ") & (np3 + ", ") & np4), "NA")


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[rml]

Do I add it at the beginning before the =Left? Can you show me how the
entire code would look?

Thanks.

 

[Tom Wickerath]

Hi RML,

What I gave you is an entire replacement for your original expression. There
is no need to include the Left function.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[rml]

I see it now. Thanks. It works fine but one small problem. How do I get
rid of the comma at the end of each? Like I had originally?

Thanks again.

 

[rml]

It works but need the comma removed at the end. It should look like this:

Now: 1234, 4534, Should be: 1234, 4534

It need to eliminate the last comma and leave the ones between.

Thanks.

 

[Tom Wickerath]

Okay, let's try a few variations. The first one (easier expression) will work
as long as the npx values are filled in from x=1 to x=4. In other words, np1
will never be null if there is a value entered for np2, and np2 will never be
null if there is a value for np3, etc. The expressions I've been suggesting
all rely on the idea of null concatentation, where null plus anything is
still null. Thus, if np2 is null, then (np2 + ", ") will also be null, as
will (", "+[np2]):

=Nz((([np1]) & (", "+[np2]) & (", "+[np3]) & (", "+[np4])),"NA")

However, I think you can see how the expression above will result in a
leading comma + space if np1 is null but np2 is not null. If this is a
possibility, ie. the numbers are *not* filled in from x=1 to x=4, then the
expression becomes a lot more complicated. This one seems to work (I think):

=IIf(Left$(Nz((([np1]) & (", "+[np2]) & (", "+[np3]) & (",
"+[np4])),"NA"),2)=", ",Mid$(Nz((([np1]) & (", "+[np2]) & (", "+[np3]) & (",
"+[np4])),"NA"),3),Nz((([np1]) & (", "+[np2]) & (", "+[np3]) & (",
"+[np4])),"NA"))



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________