Prev: Combine field entries in new field
Next: Max Query
From: Pat on 11 May 2010 10:24
Hi all,

I am trying to get query to return record like follow:

name
123 mian st.
houston, tx 77082
USA

but the result i get is as followed:
name
123 main st.
houston
tx
77082
USA
what do i need to chage in the query? the query as followed:


SELECT
vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
& Chr(10) & Chr(13) & vendor.City & " , " & vendor.State & " ' " &
vendor.Zip
& Chr(10) & Chr(13) & vendor.Country

from vendor

From: KARL DEWEY on 11 May 2010 11:10
I would say it is not possible you are getting the results you say is from
the query you posted.
Why does your query have a space preceeding the comma like this --
vendor.City & " , " & vendor.State

That comma is not showing in the results you posted. It seems you have
more than one query and are seeing the output from a different one than you
posted.

--
Build a little, test a little.


"Pat" wrote:

> Hi all,
>
> I am trying to get query to return record like follow:
>
> name
> 123 mian st.
> houston, tx 77082
> USA
>
> but the result i get is as followed:
> name
> 123 main st.
> houston
> tx
> 77082
> USA
> what do i need to chage in the query? the query as followed:
>
>
> SELECT
> vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
> & Chr(10) & Chr(13) & vendor.City & " , " & vendor.State & " ' " &
> vendor.Zip
> & Chr(10) & Chr(13) & vendor.Country
>
> from vendor
>
From: Pat on 11 May 2010 11:15
sorry the query is as followed:

SELECT
vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
& Chr(10) & Chr(13) & vendor.City & Chr(10) & Chr(13) & vendor.State &
Chr(10) & Chr(13) & vendor.Zip & Chr(10) & Chr(13) & vendor.Country

from vendor

"KARL DEWEY" wrote:

> I would say it is not possible you are getting the results you say is from
> the query you posted.
> Why does your query have a space preceeding the comma like this --
> vendor.City & " , " & vendor.State
>
> That comma is not showing in the results you posted. It seems you have
> more than one query and are seeing the output from a different one than you
> posted.
>
> --
> Build a little, test a little.
>
>
> "Pat" wrote:
>
> > Hi all,
> >
> > I am trying to get query to return record like follow:
> >
> > name
> > 123 mian st.
> > houston, tx 77082
> > USA
> >
> > but the result i get is as followed:
> > name
> > 123 main st.
> > houston
> > tx
> > 77082
> > USA
> > what do i need to chage in the query? the query as followed:
> >
> >
> > SELECT
> > vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
> > & Chr(10) & Chr(13) & vendor.City & " , " & vendor.State & " ' " &
> > vendor.Zip
> > & Chr(10) & Chr(13) & vendor.Country
> >
> > from vendor
> >
From: KARL DEWEY on 11 May 2010 11:43
Try this --
SELECT vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
& Chr(10) & Chr(13) & vendor.City & ", " & vendor.State & " " & vendor.Zip &
Chr(10) & Chr(13) & vendor.Country
FROM vendor;

--
Build a little, test a little.


"Pat" wrote:

> sorry the query is as followed:
>
> SELECT
> vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
> & Chr(10) & Chr(13) & vendor.City & Chr(10) & Chr(13) & vendor.State &
> Chr(10) & Chr(13) & vendor.Zip & Chr(10) & Chr(13) & vendor.Country
>
> from vendor
>
> "KARL DEWEY" wrote:
>
> > I would say it is not possible you are getting the results you say is from
> > the query you posted.
> > Why does your query have a space preceeding the comma like this --
> > vendor.City & " , " & vendor.State
> >
> > That comma is not showing in the results you posted. It seems you have
> > more than one query and are seeing the output from a different one than you
> > posted.
> >
> > --
> > Build a little, test a little.
> >
> >
> > "Pat" wrote:
> >
> > > Hi all,
> > >
> > > I am trying to get query to return record like follow:
> > >
> > > name
> > > 123 mian st.
> > > houston, tx 77082
> > > USA
> > >
> > > but the result i get is as followed:
> > > name
> > > 123 main st.
> > > houston
> > > tx
> > > 77082
> > > USA
> > > what do i need to chage in the query? the query as followed:
> > >
> > >
> > > SELECT
> > > vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
> > > & Chr(10) & Chr(13) & vendor.City & " , " & vendor.State & " ' " &
> > > vendor.Zip
> > > & Chr(10) & Chr(13) & vendor.Country
> > >
> > > from vendor
> > >
From: John W. Vinson on 11 May 2010 11:53
On Tue, 11 May 2010 07:24:01 -0700, Pat <Pat(a)discussions.microsoft.com> wrote:

>Hi all,
>
>I am trying to get query to return record like follow:
>
>name
>123 mian st.
>houston, tx 77082
>USA

Chr(13) & Chr(10) is how you insert a newline (carriage return - linefeed)
into a string. If you don't want the newline don't include the Chr() calls:
put in an explicit blank instead.

SELECT
vendor.VENDOR_NAME & Chr(10) & Chr(13) & vendor.Address
& Chr(10) & Chr(13) & vendor.City & " " & vendor.State &
" " & vendor.Zip & Chr(10) & Chr(13) & vendor.Country

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Combine field entries in new field
Next: Max Query