Carriage Return Error in Concatenated Field

[Guest]

I would like to group all lines of an address into a single concatenated
field. I read here that one can use vbCrLf to put a carriage return/line feed
between lines, but it is not working.

When I run the query, I get a parameter query dialog on the vbCrLf. When I
click eithr OK or Cancel and look at the combined field (on a report, as
well), there are no carriage returns.

I copied what I am using. Note that Access has put brackets around the
vbCrLf: FullAddress: [Address1] & [vbCrLf] & [Address2] & [vbCrLf] &
[Address3] & [vbCrLf] & [Street] & [vbCrLf] & [City] & ", " & [StateAbbrv] &
" " & [Zip]

I also tried this using the Plus sign as the concatenation symbol, because I
have some addresses that are blankta, and did not want to end up with "," in
the concatenated field when the address is blank. (This occurs with the
ampersand)

The test with the plus sign yields nothing in the concatenated field.

 

[fredg]

I would like to group all lines of an address into a single concatenated
field. I read here that one can use vbCrLf to put a carriage return/line feed
between lines, but it is not working.

When I run the query, I get a parameter query dialog on the vbCrLf. When I
click eithr OK or Cancel and look at the combined field (on a report, as
well), there are no carriage returns.

I copied what I am using. Note that Access has put brackets around the
vbCrLf: FullAddress: [Address1] & [vbCrLf] & [Address2] & [vbCrLf] &
[Address3] & [vbCrLf] & [Street] & [vbCrLf] & [City] & ", " & [StateAbbrv] &
" " & [Zip]

I also tried this using the Plus sign as the concatenation symbol, because I
have some addresses that are blankta, and did not want to end up with "," in
the concatenated field when the address is blank. (This occurs with the
ampersand)

The test with the plus sign yields nothing in the concatenated field.

vbCrLf is a VBA constant, and cannot be used in an Access query, only
within a VBA code module.

The query placed brackets around vbCrLf because it is expecting that
to be a field name.

In Access, (Query, Form control's control source, Report control's
control source, etc.) you must use chr(13) & chr(10) in that order:

NewField:[Address1] & chr(13) & chr(10) & [Address2] & chr(13) &
chr(10) & etc.

 

[Guest]

Fred: Ya wouldn't guess I'm a newbie, eh? Thanks. Works perfectly. After
playing around with &s and +s, I got the results I wanted without blank
spaces. Thanks again.

I would like to group all lines of an address into a single concatenated
field. I read here that one can use vbCrLf to put a carriage return/line feed
between lines, but it is not working.

When I run the query, I get a parameter query dialog on the vbCrLf. When I
click eithr OK or Cancel and look at the combined field (on a report, as
well), there are no carriage returns.

I copied what I am using. Note that Access has put brackets around the
vbCrLf: FullAddress: [Address1] & [vbCrLf] & [Address2] & [vbCrLf] &
[Address3] & [vbCrLf] & [Street] & [vbCrLf] & [City] & ", " & [StateAbbrv] &
" " & [Zip]

I also tried this using the Plus sign as the concatenation symbol, because I
have some addresses that are blankta, and did not want to end up with "," in
the concatenated field when the address is blank. (This occurs with the
ampersand)

The test with the plus sign yields nothing in the concatenated field.

vbCrLf is a VBA constant, and cannot be used in an Access query, only
within a VBA code module.

The query placed brackets around vbCrLf because it is expecting that
to be a field name.

In Access, (Query, Form control's control source, Report control's
control source, etc.) you must use chr(13) & chr(10) in that order:

NewField:[Address1] & chr(13) & chr(10) & [Address2] & chr(13) &
chr(10) & etc.