Line Break in Address block

[Sloring]

I am trying to put together an employee directory. Some employees want home information kept private so I set the report up like this below so if some infomraiton is missing, their won't be an empty space. I can not figure out how to get it to line break after Name, Home Street Address, and Zip. I have tried the Chr(13) & Chr(10) but am obviously doing something wrong. All assistance is appreciated.

=[Employee Name]+[Home Street Address]+[Home City State Zip]+[Home Phone]+[Cell Phone]+[Job Title]

 

[Scooterbug]

First, I would use & instead of +. Using the & in conjunction with the chr(13) & chr(10) should work.

 

[Sloring]

Thanks, I tried that and am getting an error message about syntax. Here is what I put.

=[Employee Name]&Chr(13)&Chr(10)& [Home Street Address]&[Home City State Zip]&[Home Phone]&[Cell Phone]&[Job Title]

 

[boblarson]

Make sure you have a space between the fields and the ampersands (&) and between the ampersands and the Chr(13) & Chr(10) code. Running them all together confuses Access sometimes.

And Scooterbug - Using the + for concatenation for things like this can be good actually. If you use it correctly it can eliminate nulls so you don't end up with spaces where the null fields are.

 

[Scooterbug]

And Scooterbug - Using the + for concatenation for things like this can be good actually. If you use it correctly it can eliminate nulls so you don't end up with spaces where the null fields are.

Huh...learn something new everyday. I generally only use + when doing calculations.

 

[Sloring]

ARGH I have been working on this all day. I checked the spaces, no luck. I did know about the + and the null values which is why I was going to use it. I just want to get the line break to work. Any other ideas? I am still getting the error message.

 

[boblarson]

ARGH I have been working on this all day. I checked the spaces, no luck. I did know about the + and the null values which is why I was going to use it. I just want to get the line break to work. Any other ideas? I am still getting the error message.

Each of the fields are correct? If necessary, can you post your database?

 

[Sloring]

It all works fine wihen I don't try to put in a line break. Its the line break that messing it up.

=[Employee Name]+[Home Street Address]+[Home City State Zip]+[Home Phone]+[Cell Phone]+[Job Title]

returns:

Dr. Duck Don1562 Pluto Rd, Saint Louis, MO 62236-2816(123) 456-7897(123) 123-1231Campus Activities Coord

 

[boblarson]

So what about:

=[Employee Name] & Chr(13) & Chr(10) + [Home Street Address] & Chr(13) & Chr(10) +[Home City State Zip] & Chr(13) & Chr(10) +[Home Phone] & Chr(13) & Chr(10) +[Cell Phone] & Chr(13) & Chr(10) +[Job Title]

 

[Sloring]

Same error. I just don't get it . I am probably missing something very obvious.I even opened a brand new report and tried your suggestion and I still get the same error message: "There was an error compiling this function. The VB module contains a syntax error. check the code then recompile." HUH?

 

[boblarson]

Same error. I just don't get it . I am probably missing something very obvious.I even opened a brand new report and tried your suggestion and I still get the same error message: "There was an error compiling this function. The VB module contains a syntax error. check the code then recompile." HUH?

Go to DEBUG and then COMPILE to see where the error lies.

 

[Sloring]

Thanks for trying. I can't get it to work. My IT is gone for the day so I will have to try on Monday.

 

[boblarson]

Thanks for trying. I can't get it to work. My IT is gone for the day so I will have to try on Monday.

That's fine, but did you do the compile to see what error it gave you (it will highlight the line in yellow).

 

[MSAccessRookie]

Thanks for trying. I can't get it to work. My IT is gone for the day so I will have to try on Monday.

If you are building the query in a VB Module, try substituting the VB Constant vbCrLf where you have Chr(13) & Chr(10)

Also, I usually use the same character ("+" or "&") for cocatenation, not both. As I recall, one of them allows concatenation of Null Fields (I think it is "+"), while the other does not.

 

[boblarson]

MSAccessRookie:
Actually, you can use a combination of them depending on the results you want.