address in one line

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

0
Utf
5/11/2010 2:24:01 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
773 Views

Similar Articles

[PageSpeed] 48

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
> 
0
Utf
5/11/2010 3:10:01 PM
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
> > 
0
Utf
5/11/2010 3:15:01 PM
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
> > > 
0
Utf
5/11/2010 3:43:01 PM
On Tue, 11 May 2010 07:24:01 -0700, Pat <Pat@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]
0
John
5/11/2010 3:53:13 PM
Reply:

Similar Artilces:

Address List Synchronization
My scenario is I have 2 Windows 2003 Active Directory Forests and 1 Exchange 2003 Organization. The users in the forest without the Exch Org have 2 AD accounts (1 for each forest) and the Exch Mailbox has granted rights to the external accounts. So mailflow works fine and so does free & busy information but the problem is sharing calendars. My problem is the GAL lists the accounts from the Forest that has the Exch Org so I cannot find a way for users in the othe Forest to share their calendars and view shared calendars, since I don't know of a way to share an object that is not...

Contacts folder versus Address Book
I'm running Outlook XP under Windows XP Pro on my home computer. I'm the only user. I want to add a new entry to my Outlook Address Book. I open the Outlook Address Book and click to make a New Entry. At the bottom of the window there is "Put this entry" and two options: Contacts, Outlook Address Book. I click Outlook Address Book and get this message: You cannot create entries for this address book. If, instead, I choose Contacts, there's no problem, I see the new contact in Outlook's the Contacts folder, but not in the Address Book. How do I add a new entry t...

send on behalf of but reply do different address?
Hi a colleague wants to send out an email-shot on behalf of her Manager but get the replies back to her own address, we are using Exchange 2003 and Outlook 2003. Does anyone know if this is possible please? Thanks Yes. Look on the Options dialog when composing a new message - use the send replies to field. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Out...

line graphs
how do I graph column B vs column A EXCEL 2007 Please go to:- http://www.pierrefondes.com/ There are 6 examples of Line Charts. Item numbers:- 69 / 55 / 53 / 49 / 48 and 44. You can take your choice as to which is best for you (they are all similar though). If my comments have helped please hit Yes. Thanks. "khw" wrote: > how do I graph column B vs column A ...

Trend line through the origin
I have three points (sometimes four) on a scatter graph. I need the formula for the best fit straight line through them that also goes exactly through the origin (0,0). an example of data would be: Test 1 X = 202.64, Y = 226.59 Test 2 X = 301.34, Y = 335.99 Test 3 X = 400.03, Y = 450.45 Alan See help for LINEST, or use =SUMPRODUCT(ydata,xdata)/SUMSQ(xdata) Jerry Alan Cocks wrote: > I have three points (sometimes four) on a scatter graph. > I need the formula for the best fit straight line through them that also > goes exactly through the origin (0,0). > >...

after adding new address they do not come up in address book
I have a very large address book and now when I add new names to it and want to send out my newsletter the addresses I have added do not come up in the list. is there a max to the number of conacts in my list? any suggestions on how to fix this problem? Please provide at least some information. Outlook does not even use an address book. Are you certain you are talking about Outlook and not some other program? What version of Outlook is this? How are you trying to add entries? What happens when you try? -- Russ Valentine [MVP-Outlook] "ginaweston" <anonymous@discussions.micr...

SFO sync issue with outlook contacts (address name filed)
Hi all, I installed SFO on several outlook clients to get the CRM database available "on the field". The synchronization (Go offline / Go online) works fine but when SFO adds the CRM contacts to the outlook contact folder, it drops the "address name" field in from contact information. All other fields (First Name, Last Name, Street 1, Street2, ZIP, ...) are copied correctly. I renamed the "address name" field as "street 0" and I am wondering if it could be the reason of the problem. 1- Can anyone tell me if the "address name" field is c...

Importing Eudora address book
I cannot import an address book from Eudora into Outlook. I don't get any error messages, it just tells me that it imported zero of zero contacts. dragonfly <anonymous@discussions.microsoft.com> wrote: > I cannot import an address book from Eudora into Outlook. > I don't get any error messages, it just tells me that it > imported zero of zero contacts. In what form is the Eudora address book? Have you tried exporting from Eudora in a CSV format? Have you tried importing it with Outlook Express and then importing it with Outlook from there? -- Brian Tillman Tha...

Addresses disappear from the address bar
I have Windows 7 x64 and IE v. 8.0.7600.16385 which works fine with one minor exception... Until recently, all the addresses I typed in the address bar were saved and were visible in a drop down window. Since this morning, only the home page address is in the address bar and drop down window contains no other addresses. Even if I type in an address, it disappears if I switch to a home page. I did not change any settings in the Tools > Internet Options... How to restore the save function on the address bar in the IE? -- Ilya Zeldes Fort Myers, Florida Ilya, Click Alt...

Attempting to perform two different sorts on one worksheet.
Great Gurus of VBA, I have a worksheet, which uses the following VBA procedure to sort th data when the last bit of entry occurs in column H. However, when want to print this spreadsheet I need the data to be sorted differentl and I am apprehensive about putting the code for the second sort int the sheet object because I am unsure about how the two might conflic with each other. Can someone give me an idea about how to have thes two different sorting routines work on the same sheet and not mess eac other up. I thought of putting a command button on the sheet to initiat the second sort but.....

Read and Reply to email using vbscript or macro basing on subject line
Hi all, What i'm trying to do is: 1. When an email arrives with certain subject line 2. Read that email subject For example subject line can be: Status 1 or Status 2 3. Depending the subject line, i need to query a sql server sproc and send some data back to the email sender. Any idea how to do this. If anybody has sample code(either macro or vbscript) that would be helpful. I would try to incorporate ADO sub to grab data from sql server, if anybody can provide code to read new mail and parse subject line and how to respond by adding body text (with results from sql query) would be very...

Exchange 5.5 Address Book Views
In Outlook2000, new message, when I click TO: button the GAL comes up. If I want to go down to the contacts I have to arrow down a whole lis of unused offices. I'm unable to delete any of them. In Exchange5.5 under our site name, -Address Book Views --Offices ---a ---b ---c ---yada all these offices are empty except for one, which has all my users bu if I try to delete the unused offices it comes back with "YOU DO NOT HAVE PERMISSION REQUIERED TO COMPLETE THIS OPRATION MICROSOFT EXCHANGE DIRECTORY ID NO. DS_E_INSUFFICIENT_ACCESS_RIGHTS" I am logged in as Administrator with f...

Why is my chart placing one column on top of another when I use a.
Why is my chart placing one column on top of another when I use a second y axis? Hi, Have a look at Jon's example, which explains how to stop the columns overlapping. http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html Cheers Andy MGutschmit wrote: > Why is my chart placing one column on top of another when I use a second y > axis? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

names ans addresses
hi to everyone, have only just joined, so please treat me as an idiot in terms of your writings to me my first question is i want to make a name and address list in excel 2007 and for each column i want name first name introd by like dislike address now what i want to do is to be able to interlink all the columns so that by the typing of word like theatre or gardens i can isolate the others and only to bring up those that like that subject please remember IDIOT language for me please :laugh:laugh:laugh:laugh -- bobbydog --------------------------...

Delete email address
I have deleted a public folder that was mail enabled. How do I delete the email address? I'm running exchange 2003 -- Thanks for your help. Kevin On Mon, 5 Feb 2007 16:20:00 -0800, Kevin <Kevin@discussions.microsoft.com> wrote: >I have deleted a public folder that was mail enabled. How do I delete the >email address? I'm running exchange 2003 If the email address is still hanging around, delete the mail-enabled object in ADUC under the Exchange System Objects container ( viewable with Advanced Features enabled. ...

Address Labels
How to create address labels in excel Kristin, If you have the label information in a table its easiest to use that as your data source for a mail merge in Word. Just make sure that each column has a unique title starting in row 1 column A and the data starts in row 2. And make sure there are no blank rows or columns anywhere else in the table and it should work fine. HTH PC "K. Bieri" <Kristin@Bobaroo.com> wrote in message news:0b4c01c34647$d00310b0$a001280a@phx.gbl... > How to create address labels in excel ...

Help with addresses
Hi... using Outlook 2000 (Office 2000/Word 2000) When creating an envelope using Word, then selecting from my address book, I am not sure where Word is getting it's info, I assume my Contact file, but the last line in the recipients address comes up United States of America- of course, it's good... but I only mail in the US and I don't enter that with each contact..and don't want that to print.. is there some way to prevent that from printing? -- B'Regards, Vinnie ...

How can I update fields in one table with fields in another table?
Hi All I have two tables: my main table and a cut-down version of the main table with newly updated values: Main ------- RefNo Document_Type Version Title ModMain ------------- RefNo Document_Type_UPDATED Version_UPDATED Title_UPDATED I can't work how to use Access's query builder to do the simple task of updating the fields in "Main" with the modified values in "ModMain" where the RefNos match. Can anyone give me a pointer or web link? I cant find any information in Access Help about performing update queries with more than one table. This seems a straight for...

Send e-mail from different addresses with one Exchange server...
I have a client that has one 2003 SBS server with Exchange enabled. This client has two companies and he has an e-mail address for each company. How can we make it so he getst to choose which address e-mail is SENT from? Is there a way to do it in Outlook 2007? "centre21" <centre21@gmail.com> escribi� en el mensaje de noticias:f8c27a5a-65fb-4862-b95f-27cd87248a36@v13g2000pro.googlegroups.com... > I have a client that has one 2003 SBS server with Exchange enabled. > This client has two companies and he has an e-mail address for each > company. How can we make it so ...

Un-numbered horizontal lines
Thank you for this service, it sure is a big help!! I copied some code that was on this site for horizontal lines in a report. The code works great except the lines are number 1-30. I need the horizontal lines but without the numbers. I tried changing some of the code but I don't understand enough to take the part out that numbers the lines. Below is the code, would someone please help me understand what to take out to change the code to make un-numbered lines? THANK YOU TONS!!!!! Private Sub Report_Page() Dim intNumLines As Integer Dim intLineNumb As Integer Dim i...

How do I get a MAC address?
Please could someone tell me the best way to get the MAC Address of a local machine? i.e. the machine of a user browsing a web page, where the webpage is run by my servers. I have had a look around and it seems to be that ActiveX and some COM calls is the only route to go but I am a rookie in this area, so any pointers/help would be greatly apprieciated. Many thanks, -Nils + The best things in life are free but then so are the worst. Here's how I do it; it should work on any platform from Win98 on: #include <iphlpapi.h> // remember to link w/ iphlpapi.lib #define...

How to select a particular smtp address (alias) for sending e-mail?
We are hosting multiple domains in our Exchange 2000 server because we have multiple business units. Each user has multiple smtp e-mail addresses per mailbox. For example, johnsmith@companya.com, johnsmith@companyb.com, and johnsmith@companyc.com etc. where the first one is the default address. How do we select a particular smtp e-mail address for sending e-mail? Thanks. Stanley Tang ...

Group addresses in Address Book
Group addresses adds an incorrect address and addresses formerly deleted. I can't correct it and the email won't send. I'm not sure what you are doing. If you open the Address Book | New | New Group | Select Members and choose the ones in the Address Book you want, that is all you should see. Are you doing it differently? Is this an old group? Try creating a new one. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Mack2" <Mack2@discussions.microsoft.com> wrote in message news:621B0FED-CD54-4634-B9E3-CDF...

Automatic contact/address inclusion in address book
Hi is there a way to include everybody who either sends me a mail or to whom i send a mail AUTOMATICALLY in the contac/address book? regards gabriel The former no one would ever want. For the latter, no current versions of Outlook have the ability to add recipients automatically to your Contacts Folder. Only the long abandoned IMO mode of Outlook 98/2000 did that. To automatically add the email address of people you respond to your Contacts Folder requires code: http://www.outlookcode.com/d/code/autoaddrecip.htm -- Russ Valentine [MVP-Outlook] "ggs" <truthnospam@icon.c...

ghost address or alias
Using Exchange 2003 about a month ago, i deleted a user (lets call him michael) from AD using ADUC, and confirmed the deletion of the mailbox. Ever since doing this, when ever a mail is sent to another user (lets call him mark), the sender receives an NDR for the user that was deleted(michael). Mark still received the mail. I have checked mark,s aliases and any mailbox rules he may have, but can't seem to find anything related to the "michael" address. Any ideas??? Tx Sean One more thing i just realised: This only occurs with calendar items (i.e appointments/invites) &qu...