Address help?

[Blackwidow]

Hi I am doing letters through reports in Access.. with fields Address 1, address 2, district, town.. If a field is blank I do not want a space... I have put in =Trim([Address1]) on all the fields... I have set all the fields to Can grow = yes and can shrink = yes.

But still I am getting blanks?

Any ideas what I am doing wrong????

 

[Mile-O]

Trim will only remove spaces on either side of a text string.

Best to build the address in code:

I.e.

Dim strAddress As String
If IsNull(Address1) = False Then
    strAddress = strAddress & Address1
Endif

If IsNull(Address2) = False Then
    strAddress = strAddress & Address2
Endif

If IsNull(Address3) = False Then
    strAddress = strAddress & Address3
Endif

MyAddress = strAddress

 

[Blackwidow]

Address (cont.)

Will this put the address on seperate lines? How do I put this in my report??

 

[Mile-O]

that won't put it on separate lines but this will:

Dim strAddress As String
If IsNull(Address1) = False Then
    strAddress = strAddress & Address1 & vbCrLf
Endif

If IsNull(Address2) = False Then
    strAddress = strAddress & Address2 & & vbCrLf
Endif

If IsNull(Address3) = False Then
    strAddress = strAddress & Address3 & & vbCrLf
Endif

MyAddress = strAddress

And, as I don't use Reports very often, I'm guessing this would go in the Page event.

Have an unbound textbox on the form (the MyAddress of the code above.

If you already have three address boxes then you can set their visible value to false.

There may, however, be another way...

 

[Blackwidow]

Hi this isnt working its does the first line Address1 but is failing at the Address2 which is blank... Any other ideas?

 

[Rich]

=[FirstName] & " " & [LastName] & Chr(13) & Chr(10) & [Address] & Chr(13) & Chr(10) & IIf(IsNull([City]),"",[City] & Chr(13) & Chr(10)) & [PostalCode]

 

[Blackwidow]

Hi Rich..

That worked perfectly... But for some reason it wont put in the post town? Any Ideas

=[Home Salutation] & Chr(13) & Chr(10) & [Address] & Chr(13) & Chr(10) & IIf(IsNull([Address- District]),"",[Address- Town] & Chr(13) & Chr(10)) & [Address- Post Code]

 

[Blackwidow]

Ignore Previous

Sorry.. just me being not very clever..

Its working brilliantly now..

thanks everyone for your help!