Hi all,

I have a table which holds lines of an address, 5 in total. These lines are stored in seperate fileds, Address1, Address2, Address3 Address4 and Address5. I would like to concatenate these fields to display in a report so that the address appears as a block. e.g.

House Number and Street
An Area
Town/City
A County
PostCode

I can imagine you saying why not use 5 text controls bound to each field. Well, the problem is not all address lines may have a value but the last line will always have a value so i would end up with a gap between say line 3 and line 5 e.g.

House Number and Street
An Area
A Town

PostCode

This is not the desired result i want on my report. I would like the missing line to be removed like below.

House Number and Street
An Area
A Town
PostCode

I have tried making a composite filed in my query that joins these 5 fileds together after checking for values in the fileds using chr(13) between each field, but when the report runs it just shows the sqaures between the different fields. It doesnt interpret the Chr value as a Carriage return. Does anybody have a way to get around this I am trying to avoid coding the report.

Jon

Code the report.

mmcdonal

By that I mean, put code in the Detail section's On Format event that looks for values in each of these fields, and then puts them in text boxes as required.

Check for:
House Number and Street
An Area
Town/City
A County
PostCode

Then have 5 text boxes.
If any of the field values are Null, then skip that data but don't skip the box.
This will get you what you want, with one caveat:

House Number and Street
An Area
Null
Null
PostCode

May result in:

House Number and Street
An Area
PostCode
...
...

So you would have empty (invisible) text boxes when there was no data. But this shouldn't effect your formatting.

I am sure there are posters who can create the text boxes with code as needed and drop these two lines in this example, but that is even more code.


mmcdonal

Sorry to keep posting...

As a practical matter, develop your report, and then make these 5 fields invisble, and then relate all your coding to these 5 invisible fields.

This way you don't have to make any calls to tables to check for the values, just to the current record on the report.

You are going to have to build the stack by checking for Nulls, though. A little time consuming but can be done.



mmcdonal

Sorry to keep posting again.

This script works to do what you want in a single text box. Perhaps in can be adapted with modifications. You need to check for null in each field and then assign either "" or the actual value to a variable. I preloaded the variables assuming Null values in two fields.

'=====

stHouse = "123 Any Street"
stArea = ""
stCity = ""
stCounty = "Howard"
stZip = "21043"

If stHouse <> "" Then
    stMessage = stHouse
End If

If stArea <> "" Then
    stMessage = stMessage & VbCrLf & stArea
End If

If stCity <> "" Then
    stMessage = stMessage & VbCrLf & stCity
End If

If stCounty <> "" Then
    stMessage = stMessage & VbCrLf & stCounty
End If

If stZip <> "" Then
    stMessage = stMessage & VbCrLf & stZip
End If

WScript.Echo stMessage
'=====

The output was:

123 Any Street
Howard
21043

HTH


mmcdonal

Shoot, this also assumes that stHouse will never be null.

Not enough work for me here this afternoon. I hope you can use some of this.

mmcdonal

Thanks for that,

I used -

    strAdd1 = Me![cl_Address1]
    strAdd2 = Me![cl_Address2]
    strAdd3 = Me![cl_Address3]
    strAdd4 = Me![cl_Address4]
    strAdd5 = Me![cl_Address5]
    strPostCode = Me![cl_Postcode]

    If strAdd1 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd1 & vbCrLf
    End If
    If strAdd2 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd2 & vbCrLf
    End If
    If strAdd3 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd3 & vbCrLf
    End If
    If strAdd4 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd4 & vbCrLf
    End If
    If strAdd5 <> "" Then
        txtClAddress1 = txtClAddress1 & strAdd5 & vbCrLf
    End If
    If strPostCode <> "" Then
        txtClAddress1 = txtClAddress1 & strPostCode & vbCrLf
    End If

This does what I want it to. Thanks again for the direction.

Jon

If you're putting them into a report in that order and there is no other field next to them, why not just set their Can Shrink properties to YES? In that way, if they're blank, they'll shrink down to nothing. No coding necessary.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks Greg,

The fields are laid out vertically, one under the other. thanks for the input though. I was hoping to add a field into a query that concatenates the fields into one adding a line feed character in between. Looks like I will have to stick with coding the report.

Jon