Tidying up an address in a report

[RichS]

I have a report in my database that prints reminder letters for appointments. The client address details are imported from another database with the fields HOUSE_NAME, HOUSE_NO, ROAD_NAME, LOCAL_NAME, COUNTY_NAM, COUNTRY_NA and POSTCODE. However, I wish to work with these so I can produce a basic 5 line address on the letters (the COUNTRY_NA is not required). As sometimes, not all of the fields have anything in them, I wish to push together the fields that have to avoid spaces in the letter. I tried this in the query the report is based on but that still leaves spaces, and also when I add the ROAD_NAME to the end of HOUSE_NO, if HOUSE_NO is a null value, ROAD_NAME disappears too. I could probably sort out both of these problems using VB code.

So my question is, where do I write the VB code, if that's what I should be doing, and if it isn't what is a better way to achieve my tidy addresses?

 

[RuralGuy]

With the fields you have described, how would you format the address?
HOUSE_NAME
HOUSE_NO ROAD_NAME
etc.

 

[RichS]

Yes, that's right. But, I wanted some way of applying these fields to new ones, for example Address1, Address2, ..., as the report is run. Some of these would be conditional so I was hoping to us code to assign the values, hence asking where any code should be, if it's possible.

However, if you have a better idea of tackling my problem, I'd be please to have a look!

 

[RuralGuy]

Show us the SQL for the query you tried please.

 

[RichS]

Hello RG,

I didn't try to change the address in the query. I was doing it in the field formatting of the report itself. The fields are exactly as they are in the imported table. This is why I asked if there was another way of doing it as I wouldn't know how to do it in sql.

Here's a typical query. Perhaps you could work with this...

SELECT NEWADD.ID, NEWADD.HOUSE_NAME, NEWADD.HOUSE_NO, NEWADD.ROAD_NAME, NEWADD.LOCAL_NAME, NEWADD.TOWN_NAME, NEWADD.COUNTY_NAM, NEWADD.POSTCODE
FROM NEWADD
ORDER BY NEWADD.ID;

I wish to have the address details in a maximum of six lines with no spaces in between, so examples would be:

House Name,
Street Name,
Locality,
Town,
County.
Postcode

or

456 Street Name,
Town,
County.
Postcode

So no blank lines if a house name or locality are not required. Similarly, if a house number is not specified. I hope I've been clear enough in my requirements...

 

[RuralGuy]

Thanks Rich,
I take it you are adding the punctuation (,.) in the controls on the report. Would I be correct in saying you are taking advantage of "can shrink" property of a report section? Is there anything else on the report lines that display the address? Are you shortening the complete letter if the address only has 4 lines?

 

[RichS]

Not sure I am! Any chance of an example?

 

[RuralGuy]

Controls on reports have CanShrink and CanGrow properties. If the section that contains the control (which also has these two properties) also has it's CanShrink property set to YES, then when the control is empty, the report will shrink.

 

[RichS]

Thanks. That's solved one of the problems. Now there aren't any gaps in the address!

I just need to sort out the problem with the house number now. The field for the second line of the address is constructed as [HOUSE_NO]+" "+[ROAD_NAME]. This works fine if both fields have something in them. However, if one is blank, for example the house has no number, the whole field is blank.

Nearly there...!

 

[RichS]

I've managed to sort this out myself!

I was using the + symbol instead of the & symbol, enforcing 'Null propogation' as it says on the website! I've now changed the second symbol to an & with perfect results!

Thanks again for your help. I've added to your rep!

 

[GaryPanic]

how about ..
Addr: [InsAdd1] & IIf(IsNull([InsAdd2]),"",Chr(13) & Chr(10) & [InsAdd2]) & IIf(IsNull([InsAdd3]),"",Chr(13) & Chr(10) & [InsAdd3]) & IIf(IsNull([InsAdd4]),"",Chr(13) & Chr(10) & [InsAdd4]) & IIf(IsNull([InsPostCode]),"",Chr(13) & Chr(10) & [InsPostCode])

addr is the ffield on my report

this will remove blanks from addresses

 

[RuralGuy]

Excellent Rich. Glad I could help and thanks for the rep points.

 

[RichS]

Thanks GaryPanic. That worked too but I've decided to stick with RG's solution.