Null fields in a calculated field

[Scott]

I have been trying to put calculated fields into a query to create a name
field that can then be used in reports, forms, etc. I have a similar problem
when I create address fields. The problem is that I get balled up in "Iif"
possibilities so it doesn't put line feeds, commas, spaces, etc. into the
name/address if there is a null field.

I presume there is a way to do this that is easier than what I do. Can you
help? Thanks.

ex. I have prefix, fname, mname, lname and suffix. If I didn't have to
worry about null fields, I could just put in prefix & " " & fname & " " &
mname & " " & lname & ", " & suffix. However, because it is possible that
there is a fname and lname only, I don't want the name to have all the spaces
and the comma for the suffix. And it is also possible to have a prefix and
lname only, or a fame, lname and suffix, etc. I end up having to create an
"Iif" clause for each scenario.

 

[Stefan Hoffmann]

hi Scott,

ex. I have prefix, fname, mname, lname and suffix. If I didn't have to
worry about null fields, I could just put in prefix & " " & fname & " " &
mname & " " & lname & ", " & suffix. However, because it is possible that
there is a fname and lname only, I don't want the name to have all the spaces
and the comma for the suffix. And it is also possible to have a prefix and
lname only, or a fame, lname and suffix, etc.

You should have taken a closer look at the help (Concatenation
Operators), use the + operator:

(prefix + " ") &
fname & " " &
(mname + " ") &
lname &
(", " + suffix)


mfG
--> stefan <--

 

[Scott]

Thanks for your help. This solved 90% of my problem. Perhaps you can get me
the rest of the way.

I discovered when looking at the results that there are some records have
fields that are not null, but they are empty (most of them are null, so your
solution works for most of the records). In those cases I end up with
unwanted characters in the concatenated fields. I was able to work around
this by adding the check to see if the field was empty (Iif(field =
"","",(field + text))). That was way simpler than what I had been doing
before, but I am hoping there is a technique that solves this problem as well.

 

[Scott]

Thanks for your help. This solved 90% of my problem. Perhaps you can get me
the rest of the way.

I discovered when looking at the results that there are some records have
fields that are not null, but they are empty (most of them are null, so your
solution works for most of the records). In those cases I end up with
unwanted characters in the concatenated fields. I was able to work around
this by adding the check to see if the field was empty (Iif(field =
"","",(field + text))). That was way simpler than what I had been doing
before, but I am hoping there is a technique that solves this problem as well.

 

[Stefan Hoffmann]

hi Scott,

I discovered when looking at the results that there are some records have
fields that are not null, but they are empty (most of them are null, so your
solution works for most of the records). In those cases I end up with
unwanted characters in the concatenated fields. I was able to work around
this by adding the check to see if the field was empty (Iif(field =
"","",(field + text))).

This will not cover all possibilities, e.g. field = " " (containing
only spaces.

So I would think about using:

Iif(Len(Trim(field)) = 0, "", field + text))

That was way simpler than what I had been doing
before, but I am hoping there is a technique that solves this problem as well.

Normally you would set the fields "Allow Zero Length" property to No.
Otherwise your Iif() is all you can do.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Scott,

I discovered when looking at the results that there are some records have
fields that are not null, but they are empty (most of them are null, so your
solution works for most of the records). In those cases I end up with
unwanted characters in the concatenated fields. I was able to work around
this by adding the check to see if the field was empty (Iif(field =
"","",(field + text))).

This will not cover all possibilities, e.g. field = " " (containing
only spaces.

So I would think about using:

Iif(Len(Trim(field)) = 0, "", field + text))

That was way simpler than what I had been doing
before, but I am hoping there is a technique that solves this problem as well.

Normally you would set the fields "Allow Zero Length" property to No.
Otherwise your Iif() is all you can do.


mfG
--> stefan <--