Calculated field (address), skipping empty lines

[kelann]

Hi, all!



I have address fields that I would like to show on a report as neatly as possible.

I would like them to appear as shown:

Address1
Address2
City, State Zip

The things I'm unsure of how to do are the following:

(1) Force a carriage return; and
(2) Skip Address2 (and the carriage return that would follow it) if Address2 is empty.

I bet this is easy, but it's a hard one to google!!!

Thanks!

--ak

[June7]

Concatenate fields in a single textbox with IIf.

Address1 & Chr(13) & Chr(10) & IIf(Not IsNull(Address2), Address2 & Chr(13) & Chr(10), "") & City & ", " & State & " " & ZIP

[kelann]

Thanks, June!

For some reason, Access is beefing about the Chr(13) and Chr(10). It doesn't matter if I use & or +, it beefs at both. It doesn't matter if I use Chr(13) or Chr$(13) -- it beefs. It's fine without them, but as soon as I add them it says the expression ... cannot be used in a calculated column.

I'm trying for a calculated text field.

I tried it as the default value for a memo field, but it won't allow that, either -- even without chr's.

If there is a better way -- to do it within the report rather than create a field -- I'm happy to do it that way. Just don't know how.

Thank you again!

--ak

[kelann]

I also tried using vb constants (vbLf, vbNewLine, vbCr, and vbLineFeed), but all to no avail -- it simply deletes the shorter ones and thinks the longer ones are fields that don't exist.

[RayMilhon]

Hate to say this but I didn't notice it anywhere above and I spent almost 30 minutes with a similar problem a couple of years ago. When I realized it I wanted to go hide. anyway Since it's a text box and your concatenating fields make sure you didn't forget the = sign in front.

[June7]

Didn't realize you were trying in Calculated field in table, haven't used those myself. Yes, put expression in textbox (I was assuming you would do that) and don't forget the equal sign as Ray pointed out.

[kelann]

You are, collectively, the best!!!

Thank you so much!

Still learning -- didn't know I could type this stuff into a text box until just today ...

[kelann]

I just wanted to thank you both again -- I am going through my tables and deleting unnecessary calculation fields and inserting them into report text boxes, instead.

Thanks for helping me to have a cleaner database!

[June7]

The Calculated field in table is not a terribly bad idea, however, it does make the db incompatible with Access 2007.

[kelann]

Thanks, June!

Quick question -- when I have the text box field in the report -- say

= FirstName + " ' + Last Name

and I have on the "Other" tab, under Name, YPLookupName.

my EventProcedures are balking -- they are asking me forthe value of YPLookupName, as if it does not exist.

I have YPLookupName set up this way on a form and on a report, and my SQL is:

DoCmd.OpenReport "GetYPInfoRep", acViewPreview, , "YPLookupName = '" & Me.YPLookupName & "'"

And it balks.

Any thoughts?

Thanks!

[kelann]

Okay, never mind, went back to the table calcs for some of the name stuff, since it's not considered bad form. All up and running again (hooray for backups)!
Thanks again!!