Text Box - Line Breaks

[stualk]

Hi folks,

Can anyone help? I am producing reports from a form linked to a database. The reports take the form of a letter and there there is an address displayed top left for the envelope window. The problem I have is that the address covers 5 fields (line 1, line 2, line 3, town, post code).

If I have a text box for each in the report there tend to be gaps in the address where certain fields are not used. Is there a way that I can use just one text box to display all the address fields, but with a line break between each one?

I currently have a way of displaying three fields on one line using this as the control source

=[Title] & " " & [First Name] & " " & [Surname]

How do I do the same but with a line break between each of the fields?

Thanks for any help you can provide.

 

[RoyVidar]

Hi!

Using for instance:

[tt]=[Title] & chr(13) & chr(10) & [First Name] & chr(13) & chr(10) & [Surname][/tt]

Roy-Vidar

 

[stualk]

Thanks for that.

What do I do if there is no text at all in a certain field though? This tends to return an error. Is there a way I can say something like this:

If any field = ""
Display nothing/or ignore

If I can do that then it should work fine.

Thanks again

 

[RoyVidar]

On my setup, it doesn't bring an error, but empty lines. To avoid that, you could experiment with

[tt]=[Title] & chr(13) + chr(10) + [First Name] & chr(13) & chr(10) & [Surname][/tt]

I e replacing the & with + for the concatination, which would then provide only two lines in stead of three in this sample if FirstName was Null.

Else, have a look at the NZ function, sample usage:

[tt]=NZ(somefield,"value if null")[/tt]

- look it up in the helpfiles, or search here - you can provide an alternative value if the variable/control is null.

If that's not sufficient, then the next step would be using code with if test as you describe, use either the IsNull function or the NZ function, or perhaps:

[tt]if len(trim(me![first name] & vbnullstring))>0 then
me!txtSomeOtherBox=me!txtSomeOtherBox & vbcrlf & me![first name]
end if[/tt]

the constants can be used in code, in stead of the chr function which is necessary in controlsources

Roy-Vidar

 

[grnzbra]

Perhaps you could loop through the fields and construct the concatination statement with an If statment that would skip the concatination when a field was null or = "". Then set the control for the display field equal to the concatination statment.

 

[stualk]

Sorry for the late reply to this post.

Many thanks to Roy-Vidar as my database is now working almost perfectly. It does everything I want it to.

The only 'problem' I now have is when I update a record it returns errors when it creates a letter. As I am using a form to input data to the database, any updates to records don't show when the report is created. This is probably because the data is only inputted to the database when you move on to the next record. When I tick the box to create a report it returns error where the address should go.

Is there anything I can do to get round this? Am I able to add a button that does two things: 1) updates the database with the data I have added, and then 2) creates a report using that data?

Any help is very much appreciated.

Stu

 

[RoyVidar]

Yup - since the report relies on table data, the record in the form must be saved prior to running the report, if the information is supposed to show up in the report.

Of several ways of saving, the following usually works:

[tt]docmd.runcommand accmdsaverecord[/tt]

- sometimes, however, it might error when there's nothing to save, if so, use:

[tt]if me.dirty then
docmd.runcommand accmdsaverecord
end if[/tt]

Then use the ordinary wizard created openreport thingie:

[tt]docmd.openreport "myrpt", acviewpreview,,"somefield=<somevalue>"[/tt]

Didn't see this last time, but you're a relative new member. Welcome to Tek-Tips! Here's a faq on how to get the most out of your membership faq181-2886. In there, you'll for instance find that new questions should usually be placed in a new thread. Check also out the different Access fora, available in the "Related Forums" box at the right, and also important, the "Keyword Search" tab at the top of each forum page;-) Good Luck!

Roy-Vidar

 

[stualk]

Thanks ever so much for that Roy, my database now works 100% perfectly now, better than I hoped it would from the off! Thanks for taking time to help me out and thanks also for the tips, I'm sure I'll be back! ;-)

Stu!

 

[shaiss]

this all seems way to complex, why not use iif(isnull([MyVar]),"",[MyVar]). I use this in all my reports to eleminate the blank fields. A concatenate would look like this:

iif(isnull([Title]),"",[Title]& " ") & iif(isnull([FirstName]),"",[FirstNamee] & " ") & iif(isnull([LastName]),"",[LastName])

I think this is the eisest way to do it.