Hello all,

I have an access report (which is a list of people) which has a field
called "Address" which is a number. I would like to select all the
distinct "Address" values in the report, lookup the values in
tblSites and return the full addresses to the report footer.

I am extremely pushed for space in the report so the address has to be
displayed as a number that the user can look up at the end of the
report.

tblSites Fields:

siteID:Number
add1:Text
add2:Text
add3:Text
town:Text
postcode:Text

Can anyone point me in the right direction?

Many thanks - David

On 08/09/2011 11:30:37, Daveo wrote:
Bearing in mind that the amount of paper for your report will be the same,
however you display it, if you want the addresses in the footer, try using a
subreport based on the same criteria that you use for the main report.

Address: SiteID & ":" & IIf([Add1]>"",[Add1]) & IIf([Add2]>"",", " & [Add2])
& IIf([Add3]>"",", " & [Add3]) & IIf([Town]>"",", " & [Town]) &
IIf([County]>"",", " & [County]) & IIf([postcode]>"",", " &
Format([PostCode],"@@@@ @@@")) & IIf([Country]>"",", " & [Country])

or if you don't have counties and countries.
Address: SiteID & ":" & IIf([MemAdd1]>"",[MemAdd1]) & IIf([MemAdd2]>"",", " &
[MemAdd2]) & IIf([MemAdd3]>"",", " & [MemAdd3]) & IIf([Town]>"",", " &
[Town]) & IIf([postcode]>"",", " & Format([PostCode],"@@@@ @@@"))

This will print your address out in one line.

Need to sort it on SiteID

Phil