(Msg. 1) Posted: Thu Jul 24, 2008 8:41 am
Post subject: Eliminate "," if no first name in cancatenated expression Add to elertz Archived from groups: microsoft>public>access>reports (more info?)
I have an expression as follows:
=lastname & ", " & firstname
but some of the expressions don't have a first name.
(Msg. 2) Posted: Fri Jul 25, 2008 12:05 am
Post subject: Re: Eliminate "," if no first name in cancatenated expression Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Try:
= [lastname] & ", " + [firstname]
This relies on a subtle difference between the 2 concatenation operators in
Access:
"A" & Null => "A"
"A" + Null => Null
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Sue" <Sue.RemoveThis@discussions.microsoft.com> wrote in message
news:DE5C384A-3FC7-45D8-B875-2370C7D20255@microsoft.com...
>I have an expression as follows:
>
> =lastname & ", " & firstname
>
> but some of the expressions don't have a first name.
>
> Can I chunk the comma in such a situation?
>
>
> THANK YOU!!!
(Msg. 3) Posted: Fri Jul 25, 2008 12:05 am
Post subject: Re: Eliminate "," if no first name in cancatenated expression Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thank you - That fixed it 100% (although I'm not clear on the subtleties
here...).
You've rescued me from the morass yet again - I appreciate your help, Allen!
--
Thanks for your time!
"Allen Browne" wrote:
> Try:
> = [lastname] & ", " + [firstname]
>
> This relies on a subtle difference between the 2 concatenation operators in
> Access:
> "A" & Null => "A"
> "A" + Null => Null
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org.
>
> "Sue" <Sue.RemoveThis@discussions.microsoft.com> wrote in message
> news:DE5C384A-3FC7-45D8-B875-2370C7D20255@microsoft.com...
> >I have an expression as follows:
> >
> > =lastname & ", " & firstname
> >
> > but some of the expressions don't have a first name.
> >
> > Can I chunk the comma in such a situation?
> >
> >
> > THANK YOU!!!
>
>
(Msg. 4) Posted: Fri Jul 25, 2008 1:14 am
Post subject: Re: Eliminate "," if no first name in cancatenated expression Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Allen Browne" <AllenBrowne RemoveThis @SeeSig.Invalid> wrote in
news:uJA#Yca7IHA.3976@TK2MSFTNGP06.phx.gbl:
> Try:
> = [lastname] & ", " + [firstname]
>
> This relies on a subtle difference between the 2 concatenation
> operators in Access:
> "A" & Null => "A"
> "A" + Null => Null
But you don't want the ", " if there is no lastname. This works in
all cases:
(Msg. 5) Posted: Fri Jul 25, 2008 5:56 am
Post subject: Re: Eliminate "," if no first name in cancatenated expression Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
That looked like a cool trick, but it didn't work when I pasted your
expression into my report. Could an operand be missing?
--
Thanks for your time!
"David W. Fenton" wrote:
> "Allen Browne" <AllenBrowne RemoveThis @SeeSig.Invalid> wrote in
> news:uJA#Yca7IHA.3976@TK2MSFTNGP06.phx.gbl:
>
> > Try:
> > = [lastname] & ", " + [firstname]
> >
> > This relies on a subtle difference between the 2 concatenation
> > operators in Access:
> > "A" & Null => "A"
> > "A" + Null => Null
>
> But you don't want the ", " if there is no lastname. This works in
> all cases:
>
> Mid(("12" + LastName) & (", " + FirstName),3)
>
> --
> David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ >
(Msg. 6) Posted: Sat Jul 26, 2008 11:54 pm
Post subject: Re: Eliminate "," if no first name in cancatenated expression Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
=?Utf-8?B?U3Vl?= <Sue.RemoveThis@discussions.microsoft.com> wrote in
news:F22FFCDD-3D01-42AE-8BAC-E63AB7EEB787@microsoft.com:
> "David W. Fenton" wrote:
>
>> Mid(("12" + LastName) & (", " + FirstName),3)
>
> That looked like a cool trick, but it didn't work when I pasted
> your expression into my report. Could an operand be missing?
I just pasted it into a query where the fields had that name, and it
worked perfectly. Are your fields named "LastName" and "FirstName"?
If not, you need to change it accordingly.
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office Other