Concatenating with and/or without a comma
in [Queries]
|
From: Owl on 4 Mar 2010 15:57 Thanks, both of you. I've got it. It is: Contact: IIf(Trim([Surname]) & ""="","",Trim([Surname]) & ", ") & Trim([FirstName]) "Owl" wrote: > That gives me SURNAME COMMA if there is only a surname. I don't want it to > have the comma if there is only a surname. > > If there is only FIRSTNAME, it isn't showing at all. I need it to show and > with no comma and no space. > > It is only if there is both, that it works correctly. > > Thanks for the input, though both of you. > > Please can you help me further. > > > > > "John W. Vinson" wrote: > > > On Thu, 4 Mar 2010 11:21:02 -0800, Owl <Owl(a)discussions.microsoft.com> wrote: > > > > >My parameter in Criteria in a query is the following: > > > > > >Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " & > > >Trim([FirstName])))) > > > > > >I am intending it to give me: > > > > > >Surname, FirstName > > >but if there is no Surname, then: > > >FirstName (without the space and the comma) > > > > > >Can somebody please correct the above parameter so that it will give me what > > >I am wanting? > > > > > >Thank you for any help. > > > > There's a sneaky shortcut: both the & and the + operators concatenate strings, > > but + "propagates nulls" - returns NULL if either argument is NULL; the & > > operator treats NULL as an empty string. Try: > > > > Contact: ([Surname] + ", ") & [FirstName] > > > > If Surname exists you'll get the expression in parentheses as "Jones, "; if it > > doesn't, then the parentheses expression will be NULL. > > -- > > > > John W. Vinson [MVP] > > . > >
From: John W. Vinson on 4 Mar 2010 16:00 On Thu, 4 Mar 2010 12:45:01 -0800, Owl <Owl(a)discussions.microsoft.com> wrote: >That gives me SURNAME COMMA if there is only a surname. I don't want it to >have the comma if there is only a surname. > >If there is only FIRSTNAME, it isn't showing at all. I need it to show and >with no comma and no space. > >It is only if there is both, that it works correctly. Sorry.... I was answering the question that you actually asked, rather than the one you intended to ask: to quote, Surname, FirstName but if there is no Surname, then: FirstName (without the space and the comma) I take it that you have three possibilities: only a surname (in which case you just want that); only a firstname (in which case you just want it); or both, in which case you want the comma? If so, you'll need some complexity: [Surname] & (Iif(IsNull([Surname]) OR IsNull([LastName], "", ", ") & [Lastname] -- John W. Vinson [MVP]
From: Owl on 4 Mar 2010 16:02 OOPS! It's still not right. I am getting the following with the previous attempt: Mike Wilson, Mary Jones, The first two are fine. However, I don't want a comma following the surname. Can you help me get rid of it, please, based on the criterion in my previous response, I would imagine. Thank you for any help. "Owl" wrote: > > Thanks, both of you. I've got it. It is: > > > Contact: IIf(Trim([Surname]) & ""="","",Trim([Surname]) & ", ") & > Trim([FirstName]) > > "Owl" wrote: > > > That gives me SURNAME COMMA if there is only a surname. I don't want it to > > have the comma if there is only a surname. > > > > If there is only FIRSTNAME, it isn't showing at all. I need it to show and > > with no comma and no space. > > > > It is only if there is both, that it works correctly. > > > > Thanks for the input, though both of you. > > > > Please can you help me further. > > > > > > > > > > "John W. Vinson" wrote: > > > > > On Thu, 4 Mar 2010 11:21:02 -0800, Owl <Owl(a)discussions.microsoft.com> wrote: > > > > > > >My parameter in Criteria in a query is the following: > > > > > > > >Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " & > > > >Trim([FirstName])))) > > > > > > > >I am intending it to give me: > > > > > > > >Surname, FirstName > > > >but if there is no Surname, then: > > > >FirstName (without the space and the comma) > > > > > > > >Can somebody please correct the above parameter so that it will give me what > > > >I am wanting? > > > > > > > >Thank you for any help. > > > > > > There's a sneaky shortcut: both the & and the + operators concatenate strings, > > > but + "propagates nulls" - returns NULL if either argument is NULL; the & > > > operator treats NULL as an empty string. Try: > > > > > > Contact: ([Surname] + ", ") & [FirstName] > > > > > > If Surname exists you'll get the expression in parentheses as "Jones, "; if it > > > doesn't, then the parentheses expression will be NULL. > > > -- > > > > > > John W. Vinson [MVP] > > > . > > >
From: Owl on 4 Mar 2010 16:05 Just to clarify: I want the comma after the Wilson example, but not after the Jones example. "Owl" wrote: > OOPS! It's still not right. I am getting the following with the previous > attempt: > > Mike > Wilson, Mary > Jones, > > The first two are fine. However, I don't want a comma following the > surname. Can you help me get rid of it, please, based on the criterion in my > previous response, I would imagine. > > Thank you for any help. > > "Owl" wrote: > > > > > Thanks, both of you. I've got it. It is: > > > > > > Contact: IIf(Trim([Surname]) & ""="","",Trim([Surname]) & ", ") & > > Trim([FirstName]) > > > > "Owl" wrote: > > > > > That gives me SURNAME COMMA if there is only a surname. I don't want it to > > > have the comma if there is only a surname. > > > > > > If there is only FIRSTNAME, it isn't showing at all. I need it to show and > > > with no comma and no space. > > > > > > It is only if there is both, that it works correctly. > > > > > > Thanks for the input, though both of you. > > > > > > Please can you help me further. > > > > > > > > > > > > > > > "John W. Vinson" wrote: > > > > > > > On Thu, 4 Mar 2010 11:21:02 -0800, Owl <Owl(a)discussions.microsoft.com> wrote: > > > > > > > > >My parameter in Criteria in a query is the following: > > > > > > > > > >Contact: IIf(Trim([Surname])="",(Trim([FirstName]),(Trim([Surname]) & ", " & > > > > >Trim([FirstName])))) > > > > > > > > > >I am intending it to give me: > > > > > > > > > >Surname, FirstName > > > > >but if there is no Surname, then: > > > > >FirstName (without the space and the comma) > > > > > > > > > >Can somebody please correct the above parameter so that it will give me what > > > > >I am wanting? > > > > > > > > > >Thank you for any help. > > > > > > > > There's a sneaky shortcut: both the & and the + operators concatenate strings, > > > > but + "propagates nulls" - returns NULL if either argument is NULL; the & > > > > operator treats NULL as an empty string. Try: > > > > > > > > Contact: ([Surname] + ", ") & [FirstName] > > > > > > > > If Surname exists you'll get the expression in parentheses as "Jones, "; if it > > > > doesn't, then the parentheses expression will be NULL. > > > > -- > > > > > > > > John W. Vinson [MVP] > > > > . > > > >
From: Owl on 4 Mar 2010 17:12 Sorry for having been confusing and thank you very much for your time. I tried what you said (with FirstName instead of LastName because Surname and LastName are the same thing), but it didn't accept one of the middle parentheses and when I took it out, it said there were too many arguments. However, in the meantime I found an entry in Queries by Ofer Cohen which worked. It is very similar to what you said. He said (to somebody else's question): Contact: ([Surname]) & IIf(Trim([Surname] & "")="",[FirstName],(", "+[FirstName])) Thank you for your help nevertheless. You guys and gals are wonderful and get so many of us out of a fix. I appreciate you all tremendously. "John W. Vinson" wrote: > On Thu, 4 Mar 2010 12:45:01 -0800, Owl <Owl(a)discussions.microsoft.com> wrote: > > >That gives me SURNAME COMMA if there is only a surname. I don't want it to > >have the comma if there is only a surname. > > > >If there is only FIRSTNAME, it isn't showing at all. I need it to show and > >with no comma and no space. > > > >It is only if there is both, that it works correctly. > > Sorry.... I was answering the question that you actually asked, rather than > the one you intended to ask: to quote, > > Surname, FirstName > but if there is no Surname, then: > FirstName (without the space and the comma) > > I take it that you have three possibilities: only a surname (in which case you > just want that); only a firstname (in which case you just want it); or both, > in which case you want the comma? If so, you'll need some complexity: > > [Surname] & (Iif(IsNull([Surname]) OR IsNull([LastName], "", ", ") & > [Lastname] > > -- > > John W. Vinson [MVP] > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Query of a subform Next: Microsoft SQL Server Management Studio / Convert Date |