Please need help with query
  Home FAQ Contact Sign in
microsoft.public.access.queries only
 
Advanced search
POPULAR GROUPS

more...

microsoft ... access.queries Profile…
 Up
Please need help with query         


Author: TotallyConfused
Date: Jun 26, 2008 21:51

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.
5 Comments
Re: Please need help with query         


Author: fredg
Date: Jun 26, 2008 22:34

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
no comments
Re: Please need help with query         


Author: TotallyConfused
Date: Jun 26, 2008 23:41

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
>
no comments
Re: Please need help with query         


Author: fredg
Date: Jun 27, 2008 00:48

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...
Show full article (1.33Kb)
no comments
Re: Please need help with query         


Author: John Spencer
Date: Jun 27, 2008 13:36

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...
Show full article (1.94Kb)
no comments
Re: Please need help with query         


Author: TotallyConfused
Date: Jun 27, 2008 21:16

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] & "")= "",""," ") & ...
Show full article (2.13Kb)
no comments