Append Query

  • Thread starter Thread starter Mike C.
  • Start date Start date
M

Mike C.

Hello All:

I have an Append Query and in one of the fields, it
combines the data of number of fields from the MEMBERS
table (FirstName, LastName, etc.) into one field
(FullName). It is set up now so that when in prints the
PrefixRank, it shows a space between the prefix and the
firstname (i.e. Mr. John Doe). There are some cases where
there will not be a PrefixRank, hence it will still have
an undesirable space before the first name. I know how to
write an IIf statement to rectify this, however what is
the syntax to place it at the beginning of the statement
(i.e. after [MEMBERS].)? The current syntax is below:


FullName: [MEMBERS].[PrefixRank] & " " & [FirstName] & IIf
(IsNull([Middleinitial]),""," " & [middleinitial] & ".")
& " " & [LastName] & IIf(IsNull([suffix]),"",", " &
[suffix]) & IIf(IsNull([Service]),"",", " & [Service])

Any help would be greaty appreciated.

Thanks in advance,

m.
 
Mike said:
Hello All:

I have an Append Query and in one of the fields, it
combines the data of number of fields from the MEMBERS
table (FirstName, LastName, etc.) into one field
(FullName). It is set up now so that when in prints the
PrefixRank, it shows a space between the prefix and the
firstname (i.e. Mr. John Doe). There are some cases where
there will not be a PrefixRank, hence it will still have
an undesirable space before the first name. I know how to
write an IIf statement to rectify this, however what is
the syntax to place it at the beginning of the statement
(i.e. after [MEMBERS].)? The current syntax is below:


FullName: [MEMBERS].[PrefixRank] & " " & [FirstName] & IIf
(IsNull([Middleinitial]),""," " & [middleinitial] & ".")
& " " & [LastName] & IIf(IsNull([suffix]),"",", " &
[suffix]) & IIf(IsNull([Service]),"",", " & [Service])
Click to expand...

IIf(IsNull(Members.PrefixRank),"", Members.PrefixRank & " ") & FirstName ...
 
It is set up now so that when in prints the
PrefixRank, it shows a space between the prefix and the
firstname (i.e. Mr. John Doe). There are some cases where
there will not be a PrefixRank, hence it will still have
an undesirable space before the first name. I know how to
write an IIf statement to rectify this, however what is
the syntax to place it at the beginning of the statement
(i.e. after [MEMBERS].)? The current syntax is below:


FullName: [MEMBERS].[PrefixRank] & " " & [FirstName]
Click to expand...

One sneaky way to do this is to take advantage of the peculiar fact
that the + and & operators both concatenate strings - but they handle
NULLs differently. + "propagates nulls" - if either string is NULL the
concatenation is NULL. & treats NULL as a zero length string.

So try

FullName: ([MEMBERS].[PrefixRank] + " ") & [FirstName] & ...

If PrefixRank is NULL, the FullName starts with FirstName; if not, you
get the blank between the PrefixRank and the name.
 
Back
Top