How to group field names in a query

  • Thread starter Thread starter Sue Hoth
  • Start date Start date
S

Sue Hoth

My database has a separate field for "Name", "Address
1", "Address 2", "City", "State" & "Zip". When I setup my
query I built an expression to combine all those fields
so they end up in the same column on my report. My
problem is they line up next to each other and I want
each think on a separate line. For instance:

Mr. Someone
1 Somewhere Place
Somewhere, IL 60000

Instead it comes out as:

Mr. Someone 1 Somewhere Place Somewhere, IL 60000

There has to be a way to program a hard return. The
expression currently looks like this.

Expr1:[Name]+"
"+[Address 1]+"
"+[Address 2]+"
"+[City]+", "+[State]+" "[Zip]

I put the quote at the end of line and hit the enter
button and then the quote at the beginning of the next
line. I was hoping this would be a hard return in but
instead I get a box that prints out between each field.

If anyone can help I would really appreciate it.

Sue
 
You need to put Chr(13) & Chr(10) to get a hard return.

If you want to eliminate problems with blank lines when there's no value for
Address 2, you can take advantage of the fact that using + as a
concatenation character propagates Nulls, while using & doesn't:

Expr1:([Name] + Chr(13) & Chr(10)) &
([Address 1] + Chr(13) & Chr(10)) &
([Address 2] + Chr(13) & Chr(10)) &
([City] + ", " + [State] + " " & [Zip])
 
vbCrLf

or chr$(13) & chr$(10)
(carriage return, line feed)

Expr1:[Name] + vbcrlf &
[Address 1]+ vbcrlf &
[Address 2]+ vbcrlf &
[City]+", " & [State] & " "[Zip]

Note that + will concatenate with null propagation,
whereas & won't. "Say whah," you ask? Null propagation
means null + something = null, whereas without null
propagation null + something = something. (So, null
& "123" = "123", whereas null + "123" = null.)


David
 
I don't believe you can use vbCrLf in a query: it'll be treated as a
variable, and you'll be prompted for a value.
 
Back
Top