Concatenate FullName based on whether a field is empty.

  • Thread starter Thread starter Billiam
  • Start date Start date
B

Billiam

I would like to concatenate a Fullname so that if there is a Preferred name
in the PrefName field, the FirstName field will not be used, AND if there is
not a PrefName field entry, then the FirstNameField is only used.

I am not sure how to do this. The following example does not provide the
PrefName,even if there is one, but still does not solve how to eliminate both
the
FirstName and PrefName fields from being in the FullName Expression...Any
help would be sincerely appreciated!!!

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]),Null) & [FirstName] & "
" & [MidName]

Billiam
 
Perhaps:

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]), [FirstName] & " " &
[MidName],[prefname])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you John for your quick response! This is really close, however, the
names which do have a preferred name do not include the MidName field...Also,
it would be really great if anyone could explain how this expression works...

Thanks again for your help, John! maybe I am doing something wrong?

John Spencer said:
Perhaps:

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]), [FirstName] & " " &
[MidName],[prefname])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I would like to concatenate a Fullname so that if there is a Preferred name
in the PrefName field, the FirstName field will not be used, AND if there is
not a PrefName field entry, then the FirstNameField is only used.

I am not sure how to do this. The following example does not provide the
PrefName,even if there is one, but still does not solve how to eliminate both
the
FirstName and PrefName fields from being in the FullName Expression...Any
help would be sincerely appreciated!!!

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]),Null) & [FirstName] & "
" & [MidName]

Billiam
Click to expand...
Click to expand...
 
the following adds the Middle name in for those names that have a preferred
name field, but duplicates the middle name for those that do not have a
preferred name field value:

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]),[FirstName] & " " &
[MidName],[prefname]) & " " & [MidName]

Billiam

Billiam said:
Thank you John for your quick response! This is really close, however, the
names which do have a preferred name do not include the MidName field...Also,
it would be really great if anyone could explain how this expression works...

Thanks again for your help, John! maybe I am doing something wrong?

John Spencer said:
Perhaps:

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]), [FirstName] & " " &
[MidName],[prefname])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I would like to concatenate a Fullname so that if there is a Preferred name
in the PrefName field, the FirstName field will not be used, AND if there is
not a PrefName field entry, then the FirstNameField is only used.

I am not sure how to do this. The following example does not provide the
PrefName,even if there is one, but still does not solve how to eliminate both
the
FirstName and PrefName fields from being in the FullName Expression...Any
help would be sincerely appreciated!!!

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]),Null) & [FirstName] & "
" & [MidName]

Billiam
Click to expand...
Click to expand...
Click to expand...
 
Check the built-in help for IIf Function. In brief, the first argument
is evaluated. If the result is True, the second argument is evaluated,
and that result is returned; if False, the third argument is evaluated
and that result is returned.

Try:

Fullname: [LastName] & ", " &
IIf(IsNull([PrefName]),
[FirstName] & " " & [MidName] ,
[prefname] & " " & [MidName])

--
Clif

Billiam said:
Thank you John for your quick response! This is really close, however,
the
names which do have a preferred name do not include the MidName
field...Also,
it would be really great if anyone could explain how this expression
works...

Thanks again for your help, John! maybe I am doing something wrong?

John Spencer said:
Perhaps:

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]), [FirstName] & "
" &
[MidName],[prefname])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I would like to concatenate a Fullname so that if there is a
Preferred name
in the PrefName field, the FirstName field will not be used, AND if
there is
not a PrefName field entry, then the FirstNameField is only used.

I am not sure how to do this. The following example does not
provide the
PrefName,even if there is one, but still does not solve how to
eliminate both
the
FirstName and PrefName fields from being in the FullName
Expression...Any
help would be sincerely appreciated!!!

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]),Null) &
[FirstName] & "
" & [MidName]

Billiam
Click to expand...
Click to expand...
Click to expand...
 
Thank you Clif!!! That does work perfectly. I will definately try to
understand the IIf function better...now that I have your example, I should
be able to see how it works. BTW, it does not seem to matter that you have
prefname in lower case...I assume this is because of something in the IIF
format which i am going to now read up on.

many thanks again for your help!

Billiam

Clif McIrvin said:
Check the built-in help for IIf Function. In brief, the first argument
is evaluated. If the result is True, the second argument is evaluated,
and that result is returned; if False, the third argument is evaluated
and that result is returned.

Try:

Fullname: [LastName] & ", " &
IIf(IsNull([PrefName]),
[FirstName] & " " & [MidName] ,
[prefname] & " " & [MidName])

--
Clif

Billiam said:
Thank you John for your quick response! This is really close, however,
the
names which do have a preferred name do not include the MidName
field...Also,
it would be really great if anyone could explain how this expression
works...

Thanks again for your help, John! maybe I am doing something wrong?

John Spencer said:
Perhaps:

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]), [FirstName] & "
" &
[MidName],[prefname])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Billiam wrote:
I would like to concatenate a Fullname so that if there is a
Preferred name
in the PrefName field, the FirstName field will not be used, AND if
there is
not a PrefName field entry, then the FirstNameField is only used.

I am not sure how to do this. The following example does not
provide the
PrefName,even if there is one, but still does not solve how to
eliminate both
the
FirstName and PrefName fields from being in the FullName
Expression...Any
help would be sincerely appreciated!!!

Fullname: [LastName] & ", " & IIf(IsNull([PrefName]),Null) &
[FirstName] & "
" & [MidName]

Billiam
Click to expand...
Click to expand...
Click to expand...
 
Thank you Clif!!! That does work perfectly. I will definately try to
understand the IIf function better...now that I have your example, I should
be able to see how it works. BTW, it does not seem to matter that you have
prefname in lower case...I assume this is because of something in the IIF
format which i am going to now read up on.
Click to expand...

Fieldnames and variable names are not case sensitive. prefname and PREFNAME
and Prefname are all the same field as far as Access is concerned.
 
Billiam said:
Thank you Clif!!! That does work perfectly. I will definately try to
understand the IIf function better...now that I have your example, I should
be able to see how it works.
Click to expand...

In that case, let's re-write the expression to help you isolate the IIf
part:

Fullname: LastName & ", " &
IIf(IsNull(PrefName), FirstName, prefname)
& " " & MidName

I also discarded the brackets because they aren't needed here.

If MidName can ever be Null, try this version to avoid a trailing space
following LastName, FirstName/prefname:

Fullname: LastName & ", " &
IIf(IsNull(PrefName), FirstName, prefname)
& (" " + MidName)
 
Thanks again for the help! This really helps me as this is such a useful
function> I really appreciate the extra help!
Very Best Regards,
Billiam
 
Back
Top