Line feed inside a SQL statement

[lachesis]

Has anyone ever created a linefeed inside the result of a SQL statement?

I'm trying to return the results from several address fields, as a combined result with linefeeds:

ie.

BuildingName, BuildingFloor
Address1,
Address2,
City, State Zipcode
Country

Im trying to concatenate all these fields into one resultant field, but with the correct formatting for form display purposes.

I've already tried fieldname1 & chr(13) & fieldname2... etc.
but this does not give my desired format.

cheers
L.

 

[scriverb]

Why would you want to do this? You can cancatenate with a line advance for the form display in the Control Source property. There is no need to complicate your query results. You may find that you will need to use the fields individually for some other use and you will then have to uncancatenate the resulting field.

Control Source Property:
[LastName] & ", " & [FirstName] & vbCrLf & [Address1] & vbCrLf & [Address2]& vbCrLf & [City] & ", " & [State] & " " & [ZipCode] & vbCrLf & [Country]

This will display the data as you requested within a rectangular textbox on your form.

Post back if you have any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[lachesis]

The reason I'm trying to use the SQL statement for the concatenation is that I have to test some fields to see if they are empty, and if they are, not to include a linefeed for the line they are one. I have a single field to populate, and make it look like a label. I do not want blank lines to appear where there should be none.

eg.
BuildingX, 19F
10 Somewhere St
East Suburb
Somewhereville, CA 90000
SomeCountry

If I put in linefeeds for every line, regardless of whether or not the source field contains data, the resultant field might end up looking like this - quite ugly really...:

eg. wrong:
[tt][blank line]
10 Somewhere St
[blank line]
Somewhereville, CA 90000
SomeCountry[/tt]

eg. right:
[tt]10 Somewhere St
Somewhereville, CA 90000
SomeCountry[/tt]

I hope that explains why I want to put the extra effort into the SQL statement to get to the right look...

L.

 

[lachesis]

Bob, I tried your suggestion anyway and it doesn't work.

Access tries to treat 'vbCrLf' as an unresolved source fieldname, and the results is #Name? in the output field.

L.

 

[scriverb]

There are a number of techniques that can be used to not print the blank lines. One that I use is to make the text boxes stacked on top of each other but with the height of each being 0". That's right. The text boxes would look like horizontal lines. I then push them all up together one right up to the other but in order of printing. I set the Can Grow property of all of them to Yes.

Now if there is data the line prints and expands to the necessary height for the font size selected for that text box. If the data is blank then the line prints but is 0" high and does not show up in the label. This works for all of the controls and will give you the look that you are requesting. There are other methods and I will try to find the threads for you and you can then choose the one that best suits your needs.

Post back with any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[lachesis]

Yeah, I was trying to avoid that bcz the text boxes have to grow & shrink - kinda messy.

I found a simple solution that requires 'chr(13)+chr(10)' in place of vbCrLf, and it works.

thanks
L.

 

[scriverb]

Yes, Chr(13) and Ch(10) do work and they are are the same as vbCrLf but how are you making your blank lines not print? The example that I gave you only requires the controls to grow. Not shrink. Just create the controls normally as you would for the label. Now change all of their heights to 0". Then push them all together. The blanks will not show and the ones with data will print in the right place. No need to manipulate the data.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???