Vbcrlf multiple lines

[rnutts]

Hi

Following a recent post I have created a piece of code to put vbcrlf between various fields to create an address field in a report, however a line is created for each field whether blank or not. If I no not want to have the blank lines, is an IIf(IsNull() statement for each field the best way to achieve this.
Code is currently
Addressfield=[clientname] & vbcrlf & [address1] & vbcrlf & [address2]
and so on
Any help greatly appreciated

Thanks

Richard

 

[Trevor G]

Richard,

I do this in a query to create an address field, see if it will help you out.

Address: IIf([Booking Address 1] Is Not Null,[Booking Address 1] & Chr(13) & Chr(10),Null) & IIf([Booking Address 2] Is Not Null,[Booking Address 2] & Chr(13) & Chr(10),Null) & IIf([Booking Address 3] Is Not Null,[Booking Address 3] & Chr(13) & Chr(10),Null) & IIf([Booking Address 4] Is Not Null,[Booking Address 4] & Chr(13) & Chr(10),Null) & IIf([Booking Address 5] Is Not Null,[Booking Address 5] & Chr(13) & Chr(10),Null) & IIf([Booking Address 6] Is Not Null,[Booking Address 6] & Chr(13) & Chr(10),Null)

 

[JamesMcS]

Try

Addressfield=[clientname] & iif([address1] is null, vbcrlf, vbcrlf & [address1]) & iif([address2] is null, vbcrlf, vbcrlf & [address2])

 

[JamesMcS]

Just beat me trevor G

 

[Trevor G]

Just beat me trevor G

Hope all is well with you James?

 

[JamesMcS]

The other option would be to have your fields as text boxes seprately on the report, then setting the canshrink property to true. This way, if you position the controls on your report so there's a gap in between, you'll be left with the gap if the field is null.

 

[JamesMcS]

Hope all is well with you James?

Doing good fella - just finished renovating a flat so I've got me evenings back! You?

 

[Trevor G]

Sounds as though you've been having fun James !

Yes not so bad here in sunny Tamworth.

 

[JamesMcS]

Dude, it sucked - everything I touched revealed more bodged stuff underneath it.... Still - all finished, and so far no fires or leaks....

 

[stopher]

Use the + as a concatenator. If either side of the + is null then the result is null. This can be used to great effect to eliminate blank lines:

Addressfield = ([clientname] + vbCrLf) & _
            ([address1] + vbCrLf) & _
            ([address2] + vbCrLf) & _
            ([address3] + vbCrLf) & _
            ([address4])

hth
Chris

 

[JamesMcS]

Didn't know that! Nice one Stopher!

 

[Trevor G]

Use the + as a concatenator. If either side of the + is null then the result is null. This can be used to great effect to eliminate blank lines:

Addressfield = ([clientname] + vbCrLf) & _
            ([address1] + vbCrLf) & _
            ([address2] + vbCrLf) & _
            ([address3] + vbCrLf) & _
            ([address4])

hth
Chris

That seems easier to add in. Nice one Chris.

 

[boblarson]

And just a note about James' response - you can't use vbCrLf in a query. You can in VBA but not in a query. In a query you would need to use Chr(13) & Chr(10) as the equivalent.

 

[rnutts]

Thanks to everyone for their contributions, I used Stopher's version as it was the shortest piece of code and it worked perfectly

Richard

 

[JamesMcS]

Whoops I thought we were talking about a control source for a field... lesson 1 - read the bloody original post

 

[boblarson]

Whoops I thought we were talking about a control source for a field... lesson 1 - read the bloody original post

You can't use vbCrLf in a control source either

 

[JamesMcS]

There's a reason you've got a great big star in your signature

 

[Rich]

And just a note about James' response - you can't use vbCrLf in a query. You can in VBA but not in a query. In a query you would need to use Chr(13) & Chr(10) as the equivalent.

Chr10 etc don't work in 2010

 

[JamesMcS]

Hmm a quick google revealed a post that said Chr wasn't in the expression builder of Access 2010... Bob? (I use 07)....

 

[boblarson]

Chr10 etc don't work in 2010

Please explain where you are saying it doesn't work. It works fine for me in 2010 ( Chr(13) & Chr(10) ) and I just did a test with it right now. Works fine in the query, form and vba.