splicing names together

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
 
P

PC Datasheet

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]
 
R

Ron Weiner

Try

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

In your Sql Statement

Ron W
 
C

Cheryl Fischer

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,
 
J

JohnE

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
 
R

Ron Weiner

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...
 
P

PC Datasheet

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...
 
J

JohnE

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...
 
J

JohnE

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...
 
J

JohnE

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Plz Help! Create table when submit form 0
Getting Multiple Owners Names for Single Pieces of Property 8
Preventing duplicate record entries where two fields are involved 13
Access Creating names from fields in access database. 0
'Unspecified Error' Message in NotInList Event Proc 2
listbox requery 2
Firstname plus Middlename?? 10
I need to convert name 3 part name into Last, First MiddleNameorMI 5

Top