sql concatenate

[C L Humphreys]

Hi,

I would like a sql query that would return the results of 3 selections as
one column. For example I may have

select title, forename, surname from details;

but would like the results in one column, with the fields concatenated (with
a space preferable between!)

This should be simple, right? :)

Thanks,
Chris

[Stewart Allen]

Re: sql concatenate

The SQL behind the query should read
SELECT [Title] & " " & [ForeName] & " " & [Surname] AS WholeName
FROM Details;

To do it in the design grid:
Type the new field name followed by a colon then type the first name of the
field followed by the &, then 2 double quotes with a space between them,
followed by the second field name etc.

Stewart


"C L Humphreys" <clhumphreys@to ofgib.moc> wrote in message
news:bkbotn$5d5 $1@ucsnew1.ncl. ac.uk...[color=blue]
> Hi,
>
> I would like a sql query that would return the results of 3 selections as
> one column. For example I may have
>
> select title, forename, surname from details;
>
> but would like the results in one column, with the fields concatenated[/color]
(with[color=blue]
> a space preferable between!)
>
> This should be simple, right? :)
>
> Thanks,
> Chris
>
>[/color]

[Tim Mills-Groninger]

Re: sql concatenate

Alternate: SELECT Title + ' ' & Forename & ' ' & Surname) AS FullName FROM
details;

Works great for middle names!

Tim Mills-Groninger

"Allen Browne" <abrowne1_SpamT rap@bigpond.net .au> wrote in message
news:Xseab.1095 70$bo1.65189@ne ws-server.bigpond. net.au...[color=blue]
> SELECT Trim(Title & ' ' & Forename & ' ' & Surname) AS FullName FROM
> details;
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
>
> "C L Humphreys" <clhumphreys@to ofgib.moc> wrote in message
> news:bkbotn$5d5 $1@ucsnew1.ncl. ac.uk...[color=green]
> > Hi,
> >
> > I would like a sql query that would return the results of 3 selections[/color][/color]
as[color=blue][color=green]
> > one column. For example I may have
> >
> > select title, forename, surname from details;
> >
> > but would like the results in one column, with the fields concatenated[/color]
> (with[color=green]
> > a space preferable between!)[/color][/color]