splicing names together

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I am having a heck of a time getting the FirstName
MiddleName and LastName together in several different
instances. Not all names will have a middle name or
initial. On a main form the names are separated out for
entering. The different instances are;

1) throughout the app there is a pop up and the name
should be together in a textbox
2) the drop down lists should also have the names together
3) several listboxes should have the name together

All 3 of these are using a sql to populate. The sql
works fine with the exception of the line in which I try
to put the names together. I can get it with just one
part but I'm failing trying all 3 together. Also, if
there is or isn't a middle name or initial, how is the
proper spacing accomplished?
Thanks to anyone who responds.
*** John
 
Hi John,

Put the following expression in a query field:

Person:[FirstName] & " " & IIF(IsNull([MI]),"",[MI] & " ") & [LastName]

If you put LastName first, you then don't need to worry about the middle Name:

Person:[Lastname] & ", " & [FirstName] & " " & [MI]
 
Try

FirstName & IIf(Not IsNull(MiddleName),' ' & MiddleName,'') & ' ' &
LastName AS FullName

In your Sql Statement

Ron W
 
John,

Here is another "flavor" ...

([Firstname]+" ") & ([Middle]+" ") & ([Lastname]) as FullName

If [Middle] is null, then null+" " = null and no extra space will appear in
your FullName string.

hth,
 
Ron, I used your suggestion (nothing personal to the
others that responded) and plugged it in on the pop up
address form. The popup form is not bound to any table.
The information used comes from the usrtblContacts table.
Here is the sql I use and I put your suggestion in where
the Name field was. I get a parameter asking me name
which is coming from the bottom of the sql. I changed the
Name to FullName but the parameter still shows. What am I
missing? Your suggestion works well in the listbox and
drop down list. But the textbox is giving me fits.
Forms![frmContactPerson].RecordSource = _
"SELECT " & _
"ClientCode, " & _
"FirstName & IIf(Not IsNull(MiddleName),' ' &
MiddleName,' ') & ' ' & LastName AS FullName, " & _
"ContactType, " & _
"Function, " & _
"StreetAddress1, " & _
"StreetAddress2, " & _
"StreetAddressCity, " & _
"StreetAddressState, " & _
"StreetAddressZipCode, " & _
"MailingAddress1, " & _
"MailingAddress2, " & _
"MailingAddressCity, " & _
"MailingAddressState, " & _
"MailingAddressZipCode, " & _
"Telephone, " & _
"TelephoneExtension, " & _
"Fax, " & _
"MobilePhone, " & _
"Email " & _
"FROM usrtblContacts " & _
"WHERE (((ClientCode) = [Forms]![usrfrmBilling]!
[ClientCode])) " & _
"AND (Name)=[Forms]![usrfrmBilling]!
[InvoiceCopiesTo];"

*** John
 
Looks like your where clause is refering to Name.

AND (Name)=[Forms]![usrfrmBilling]![InvoiceCopiesTo];"

Try changing that to:

AND (FullName)=[Forms]![usrfrmBilling]![InvoiceCopiesTo];"

Unless there is another field in usrtblContacts called Name

Ron W

JohnE said:
Ron, I used your suggestion (nothing personal to the
others that responded) and plugged it in on the pop up
address form. The popup form is not bound to any table.
The information used comes from the usrtblContacts table.
Here is the sql I use and I put your suggestion in where
the Name field was. I get a parameter asking me name
which is coming from the bottom of the sql. I changed the
Name to FullName but the parameter still shows. What am I
missing? Your suggestion works well in the listbox and
drop down list. But the textbox is giving me fits.
Forms![frmContactPerson].RecordSource = _
"SELECT " & _
"ClientCode, " & _
"FirstName & IIf(Not IsNull(MiddleName),' ' &
MiddleName,' ') & ' ' & LastName AS FullName, " & _
"ContactType, " & _
"Function, " & _
"StreetAddress1, " & _
"StreetAddress2, " & _
"StreetAddressCity, " & _
"StreetAddressState, " & _
"StreetAddressZipCode, " & _
"MailingAddress1, " & _
"MailingAddress2, " & _
"MailingAddressCity, " & _
"MailingAddressState, " & _
"MailingAddressZipCode, " & _
"Telephone, " & _
"TelephoneExtension, " & _
"Fax, " & _
"MobilePhone, " & _
"Email " & _
"FROM usrtblContacts " & _
"WHERE (((ClientCode) = [Forms]![usrfrmBilling]!
[ClientCode])) " & _
"AND (Name)=[Forms]![usrfrmBilling]!
[InvoiceCopiesTo];"

*** John


-----Original Message-----
Try

FirstName & IIf(Not IsNull(MiddleName),' ' & MiddleName,'') & ' ' &
LastName AS FullName

In your Sql Statement

Ron W





.
Click to expand...
Click to expand...
 
John,

"Name" is an Access reserved word and this could be the reason for your problem.
Try changing the field name.

Steve
PC Datasheet

JohnE said:
Ron, I used your suggestion (nothing personal to the
others that responded) and plugged it in on the pop up
address form. The popup form is not bound to any table.
The information used comes from the usrtblContacts table.
Here is the sql I use and I put your suggestion in where
the Name field was. I get a parameter asking me name
which is coming from the bottom of the sql. I changed the
Name to FullName but the parameter still shows. What am I
missing? Your suggestion works well in the listbox and
drop down list. But the textbox is giving me fits.
Forms![frmContactPerson].RecordSource = _
"SELECT " & _
"ClientCode, " & _
"FirstName & IIf(Not IsNull(MiddleName),' ' &
MiddleName,' ') & ' ' & LastName AS FullName, " & _
"ContactType, " & _
"Function, " & _
"StreetAddress1, " & _
"StreetAddress2, " & _
"StreetAddressCity, " & _
"StreetAddressState, " & _
"StreetAddressZipCode, " & _
"MailingAddress1, " & _
"MailingAddress2, " & _
"MailingAddressCity, " & _
"MailingAddressState, " & _
"MailingAddressZipCode, " & _
"Telephone, " & _
"TelephoneExtension, " & _
"Fax, " & _
"MobilePhone, " & _
"Email " & _
"FROM usrtblContacts " & _
"WHERE (((ClientCode) = [Forms]![usrfrmBilling]!
[ClientCode])) " & _
"AND (Name)=[Forms]![usrfrmBilling]!
[InvoiceCopiesTo];"

*** John


-----Original Message-----
Try

FirstName & IIf(Not IsNull(MiddleName),' ' & MiddleName,'') & ' ' &
LastName AS FullName

In your Sql Statement

Ron W





.
Click to expand...
Click to expand...
 
Ron, tried the FullName as well but still get the
parameter box showing but now asking about FullName. I
tried typing in the name into the parameter box but
nothing appeared in the popup form. The line in the sql
is the same as what appears in the drop down list box sql
and it works fine. I renamed the txtbox on the popup to
FullName
Other suggestions?
*** John


-----Original Message-----
Looks like your where clause is refering to Name.

AND (Name)=[Forms]![usrfrmBilling]![InvoiceCopiesTo];"

Try changing that to:

AND (FullName)=[Forms]![usrfrmBilling]![InvoiceCopiesTo];"

Unless there is another field in usrtblContacts called Name

Ron W

Ron, I used your suggestion (nothing personal to the
others that responded) and plugged it in on the pop up
address form. The popup form is not bound to any table.
The information used comes from the usrtblContacts table.
Here is the sql I use and I put your suggestion in where
the Name field was. I get a parameter asking me name
which is coming from the bottom of the sql. I changed the
Name to FullName but the parameter still shows. What am I
missing? Your suggestion works well in the listbox and
drop down list. But the textbox is giving me fits.
Forms![frmContactPerson].RecordSource = _
"SELECT " & _
"ClientCode, " & _
"FirstName & IIf(Not IsNull (MiddleName),' ' &
MiddleName,' ') & ' ' & LastName AS FullName, " & _
"ContactType, " & _
"Function, " & _
"StreetAddress1, " & _
"StreetAddress2, " & _
"StreetAddressCity, " & _
"StreetAddressState, " & _
"StreetAddressZipCode, " & _
"MailingAddress1, " & _
"MailingAddress2, " & _
"MailingAddressCity, " & _
"MailingAddressState, " & _
"MailingAddressZipCode, " & _
"Telephone, " & _
"TelephoneExtension, " & _
"Fax, " & _
"MobilePhone, " & _
"Email " & _
"FROM usrtblContacts " & _
"WHERE (((ClientCode) = [Forms]![usrfrmBilling]!
[ClientCode])) " & _
"AND (Name)=[Forms]![usrfrmBilling]!
[InvoiceCopiesTo];"

*** John


-----Original Message-----
Try

FirstName & IIf(Not IsNull(MiddleName),' ' & MiddleName,'') & ' ' &
LastName AS FullName

In your Sql Statement

Ron W


I am having a heck of a time getting the FirstName
MiddleName and LastName together in several different
instances. Not all names will have a middle name or
initial. On a main form the names are separated out for
entering. The different instances are;

1) throughout the app there is a pop up and the name
should be together in a textbox
2) the drop down lists should also have the names together
3) several listboxes should have the name together

All 3 of these are using a sql to populate. The sql
works fine with the exception of the line in which I try
to put the names together. I can get it with just one
part but I'm failing trying all 3 together. Also, if
there is or isn't a middle name or initial, how is the
proper spacing accomplished?
Thanks to anyone who responds.
*** John


.
Click to expand...
Click to expand...


.
Click to expand...
 
I changed the textbox name to FullName, same as what is in
the sql where clause but still get the parameter box.
*** John
-----Original Message-----
John,

"Name" is an Access reserved word and this could be the reason for your problem.
Try changing the field name.

Steve
PC Datasheet

Ron, I used your suggestion (nothing personal to the
others that responded) and plugged it in on the pop up
address form. The popup form is not bound to any table.
The information used comes from the usrtblContacts table.
Here is the sql I use and I put your suggestion in where
the Name field was. I get a parameter asking me name
which is coming from the bottom of the sql. I changed the
Name to FullName but the parameter still shows. What am I
missing? Your suggestion works well in the listbox and
drop down list. But the textbox is giving me fits.
Forms![frmContactPerson].RecordSource = _
"SELECT " & _
"ClientCode, " & _
"FirstName & IIf(Not IsNull (MiddleName),' ' &
MiddleName,' ') & ' ' & LastName AS FullName, " & _
"ContactType, " & _
"Function, " & _
"StreetAddress1, " & _
"StreetAddress2, " & _
"StreetAddressCity, " & _
"StreetAddressState, " & _
"StreetAddressZipCode, " & _
"MailingAddress1, " & _
"MailingAddress2, " & _
"MailingAddressCity, " & _
"MailingAddressState, " & _
"MailingAddressZipCode, " & _
"Telephone, " & _
"TelephoneExtension, " & _
"Fax, " & _
"MobilePhone, " & _
"Email " & _
"FROM usrtblContacts " & _
"WHERE (((ClientCode) = [Forms]![usrfrmBilling]!
[ClientCode])) " & _
"AND (Name)=[Forms]![usrfrmBilling]!
[InvoiceCopiesTo];"

*** John


-----Original Message-----
Try

FirstName & IIf(Not IsNull(MiddleName),' ' & MiddleName,'') & ' ' &
LastName AS FullName

In your Sql Statement

Ron W


I am having a heck of a time getting the FirstName
MiddleName and LastName together in several different
instances. Not all names will have a middle name or
initial. On a main form the names are separated out for
entering. The different instances are;

1) throughout the app there is a pop up and the name
should be together in a textbox
2) the drop down lists should also have the names together
3) several listboxes should have the name together

All 3 of these are using a sql to populate. The sql
works fine with the exception of the line in which I try
to put the names together. I can get it with just one
part but I'm failing trying all 3 together. Also, if
there is or isn't a middle name or initial, how is the
proper spacing accomplished?
Thanks to anyone who responds.
*** John


.
Click to expand...
Click to expand...


.
Click to expand...
 
I got it to work. In the where clause I replaced AND
(FullName) with the line in the SELECT part splicing the
fields together less the AS FullName.
Still a thanks for getting me as far as you did.
*** John

-----Original Message-----
Looks like your where clause is refering to Name.

AND (Name)=[Forms]![usrfrmBilling]![InvoiceCopiesTo];"

Try changing that to:

AND (FullName)=[Forms]![usrfrmBilling]![InvoiceCopiesTo];"

Unless there is another field in usrtblContacts called Name

Ron W

Ron, I used your suggestion (nothing personal to the
others that responded) and plugged it in on the pop up
address form. The popup form is not bound to any table.
The information used comes from the usrtblContacts table.
Here is the sql I use and I put your suggestion in where
the Name field was. I get a parameter asking me name
which is coming from the bottom of the sql. I changed the
Name to FullName but the parameter still shows. What am I
missing? Your suggestion works well in the listbox and
drop down list. But the textbox is giving me fits.
Forms![frmContactPerson].RecordSource = _
"SELECT " & _
"ClientCode, " & _
"FirstName & IIf(Not IsNull (MiddleName),' ' &
MiddleName,' ') & ' ' & LastName AS FullName, " & _
"ContactType, " & _
"Function, " & _
"StreetAddress1, " & _
"StreetAddress2, " & _
"StreetAddressCity, " & _
"StreetAddressState, " & _
"StreetAddressZipCode, " & _
"MailingAddress1, " & _
"MailingAddress2, " & _
"MailingAddressCity, " & _
"MailingAddressState, " & _
"MailingAddressZipCode, " & _
"Telephone, " & _
"TelephoneExtension, " & _
"Fax, " & _
"MobilePhone, " & _
"Email " & _
"FROM usrtblContacts " & _
"WHERE (((ClientCode) = [Forms]![usrfrmBilling]!
[ClientCode])) " & _
"AND (Name)=[Forms]![usrfrmBilling]!
[InvoiceCopiesTo];"

*** John


-----Original Message-----
Try

FirstName & IIf(Not IsNull(MiddleName),' ' & MiddleName,'') & ' ' &
LastName AS FullName

In your Sql Statement

Ron W


I am having a heck of a time getting the FirstName
MiddleName and LastName together in several different
instances. Not all names will have a middle name or
initial. On a main form the names are separated out for
entering. The different instances are;

1) throughout the app there is a pop up and the name
should be together in a textbox
2) the drop down lists should also have the names together
3) several listboxes should have the name together

All 3 of these are using a sql to populate. The sql
works fine with the exception of the line in which I try
to put the names together. I can get it with just one
part but I'm failing trying all 3 together. Also, if
there is or isn't a middle name or initial, how is the
proper spacing accomplished?
Thanks to anyone who responds.
*** John


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top