Prev: Query of a subform
Next: Microsoft SQL Server Management Studio / Convert Date
From: Owl on 4 Mar 2010 14:21
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.
From: Daryl S on 4 Mar 2010 14:36
Owl -

The difference is in the parentheses:

Contact: IIf(Trim([Surname])="",Trim([FirstName]),(Trim([Surname]) & ", " &
Trim([FirstName])))

--
Daryl S


"Owl" 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.
From: John W. Vinson on 4 Mar 2010 14:56
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 15:18
I see that I had an extra parenthesis at the end, but I put in what you said
in my query and it gives me COMMA SPACE FIRSTNAME when there is only a
FirstName, and not just FirstName without the comma and the space. It looks
so right to me, and I can't see where it is wrong.

"Daryl S" wrote:

> Owl -
>
> The difference is in the parentheses:
>
> Contact: IIf(Trim([Surname])="",Trim([FirstName]),(Trim([Surname]) & ", " &
> Trim([FirstName])))
>
> --
> Daryl S
>
>
> "Owl" 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.
From: Owl on 4 Mar 2010 15:45
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]
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Query of a subform
Next: Microsoft SQL Server Management Studio / Convert Date