concatenation issue

[SuicidalDriver]

Hello,



I can't seem to get my concatenation query to work, I need it to take the separate parts of the address and put them into one field

This is what I have:
CAddress1: [CStreetNumber1] & " " & [CStreetName1] & " " & [CSuburb1] & " " & [CCity1] & " " & [CPostCode1]


Any help would be greatly appreciated


Thanks
Andy

[Bob Fitz]

In what way does it not work. What is wrong with the the data that is returned.

[SuicidalDriver]

It only shows the field name (CAddress1) when I push run, so it doesn't actually return data

[Bob Fitz]

Perhaps some of your fields contain Null values. What result do you get if you use:
CAddress1: Nz([CStreetNumber1],"") & " " & Nz([CStreetName1],"") & " " & Nz([CSuburb1],"") & " " & Nz([CCity1],"") & " " & Nz([CPostCode1],"")

[SuicidalDriver]

If I use the above it just shows the same thing, I've also just checked the fields I'm trying to concat and they all have a value in them.

[Bob Fitz]

What do you get if you try:
CAddress1: Nz([CStreetNumber1],"")
And what do you get if you try:
CAddress1: Nz([CStreetNumber1],"") & " " & Nz([CStreetName1],"")

[June7]

Post the entire query SQL statement for analysis.

I see nothing wrong with the original syntax. Strings should still concatenate even if any fields are null.

[drunkenneo]

Hello,

I can't seem to get my concatenation query to work, I need it to take the separate parts of the address and put them into one field

This is what I have:
CAddress1: [CStreetNumber1] & " " & [CStreetName1] & " " & [CSuburb1] & " " & [CCity1] & " " & [CPostCode1]


Any help would be greatly appreciated


Thanks
Andy

Are you trying to display it in the form?

[SuicidalDriver]

currently im just trying to get it to display the concatenated field when I press run, this image shows the query in design view
.

[Symlink]

You can switch view to have the full SQL statement using upper left icon (left of the red !) and selecting SQL view.

[June7]

Only the one table (no joining) in the query, then that expression should work.

If you want to provide db for analysis, follow instructions at bottom of my post.

[SuicidalDriver]

thanks for the help, and the tip about viewing the full sql statement, it helped a lot, turned out what the issue was is it had "FROM customer, job;" when it should have been "FROM customer;" I changed that and it's fixed it