|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 26th, 2008, 08:51 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Please need help with query
How do I write a query that say the following: If there is a middle name,
then Middle Name and space. If there is not a middle name, then no space. I am trying to concactenate First, Middle and Last Names. But do not want a double space if there is no middle name. Any help will be great appreciated Thank you. 
|
|
#2
June 26th, 2008, 09:34 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Please need help with query
On Thu, 26 Jun 2008 12:51:37 -0700, TotallyConfused wrote:
How do I write a query that say the following: If there is a middle name, then Middle Name and space. If there is not a middle name, then no space. I am trying to concactenate First, Middle and Last Names. But do not want a double space if there is no middle name. Any help will be great appreciated Thank you. CominedNames:[First Name] & (" "+[MiddleName]) & " " & [LastName] -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#3
June 26th, 2008, 10:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Please need help with query
Thank you very much for your response. However, this works fine when there
is a MidNM or Initial and period. But when there is no MidNM, then it leaves 2 spaces instead of just one space between first Name and last name. Can this be fixed? if so how? Thank you. "fredg" wrote: On Thu, 26 Jun 2008 12:51:37 -0700, TotallyConfused wrote: How do I write a query that say the following: If there is a middle name, then Middle Name and space. If there is not a middle name, then no space. I am trying to concactenate First, Middle and Last Names. But do not want a double space if there is no middle name. Any help will be great appreciated Thank you. CominedNames:[First Name] & (" "+[MiddleName]) & " " & [LastName] -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#4
June 26th, 2008, 11:48 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Please need help with query
On Thu, 26 Jun 2008 14:41:03 -0700, TotallyConfused wrote:
Thank you very much for your response. However, this works fine when there is a MidNM or Initial and period. But when there is no MidNM, then it leaves 2 spaces instead of just one space between first Name and last name. Can this be fixed? if so how? Thank you. "fredg" wrote: On Thu, 26 Jun 2008 12:51:37 -0700, TotallyConfused wrote: How do I write a query that say the following: If there is a middle name, then Middle Name and space. If there is not a middle name, then no space. I am trying to concactenate First, Middle and Last Names. But do not want a double space if there is no middle name. Any help will be great appreciated Thank you. CominedNames:[First Name] & (" "+[MiddleName]) & " " & [LastName] -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Did you use the + symbol and parenthesis as I posted? (" "+[MiddleName]) It works fine for me. John Smith John C. Smith Please copy and paste the exact expression you are using. You can also use: CombinedNames:[FirstName] & " " & IIf(IsNull([MiddleInitial]),[LastName],[MiddleInitial] & " " & [LastName]) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail
|
|
#5
June 27th, 2008, 12:36 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Please need help with query
If FredG's suggestion did not work and you still get two spaces then you will
need to use an expression like the following. CombinedNames:[First Name] & IIF({MiddleName] & "")= "",""," ") & [MiddleName]) & " " & [LastName] Fred's version relies on MiddleName being NULL. It could be a zero-length string which is a different thing. The above IIF clause tests for both nulls and zero-length strings. You can check to see if Zero-length strings are allowed by looking at the field properties in your table. -- John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County fredg wrote: On Thu, 26 Jun 2008 14:41:03 -0700, TotallyConfused wrote: Thank you very much for your response. However, this works fine when there is a MidNM or Initial and period. But when there is no MidNM, then it leaves 2 spaces instead of just one space between first Name and last name. Can this be fixed? if so how? Thank you. "fredg" wrote: On Thu, 26 Jun 2008 12:51:37 -0700, TotallyConfused wrote: How do I write a query that say the following: If there is a middle name, then Middle Name and space. If there is not a middle name, then no space. I am trying to concactenate First, Middle and Last Names. But do not want a double space if there is no middle name. Any help will be great appreciated Thank you. CominedNames:[First Name] & (" "+[MiddleName]) & " " & [LastName] -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Did you use the + symbol and parenthesis as I posted? (" "+[MiddleName]) It works fine for me. John Smith John C. Smith Please copy and paste the exact expression you are using. You can also use: CombinedNames:[FirstName] & " " & IIf(IsNull([MiddleInitial]),[LastName],[MiddleInitial] & " " & [LastName])
|
|
#6
June 27th, 2008, 08:16 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Please need help with query
Thank you very much for all your help. Very much appreciated!!
"John Spencer" wrote: If FredG's suggestion did not work and you still get two spaces then you will need to use an expression like the following. CombinedNames:[First Name] & IIF({MiddleName] & "")= "",""," ") & [MiddleName]) & " " & [LastName] Fred's version relies on MiddleName being NULL. It could be a zero-length string which is a different thing. The above IIF clause tests for both nulls and zero-length strings. You can check to see if Zero-length strings are allowed by looking at the field properties in your table. -- John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County fredg wrote: On Thu, 26 Jun 2008 14:41:03 -0700, TotallyConfused wrote: Thank you very much for your response. However, this works fine when there is a MidNM or Initial and period. But when there is no MidNM, then it leaves 2 spaces instead of just one space between first Name and last name. Can this be fixed? if so how? Thank you. "fredg" wrote: On Thu, 26 Jun 2008 12:51:37 -0700, TotallyConfused wrote: How do I write a query that say the following: If there is a middle name, then Middle Name and space. If there is not a middle name, then no space. I am trying to concactenate First, Middle and Last Names. But do not want a double space if there is no middle name. Any help will be great appreciated Thank you. CominedNames:[First Name] & (" "+[MiddleName]) & " " & [LastName] -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Did you use the + symbol and parenthesis as I posted? (" "+[MiddleName]) It works fine for me. John Smith John C. Smith Please copy and paste the exact expression you are using. You can also use: CombinedNames:[FirstName] & " " & IIf(IsNull([MiddleInitial]),[LastName],[MiddleInitial] & " " & [LastName])
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
