Name & address concatenation 1

[ramondrumon]

I have my Name and address table and this goes into my different forms, so it is foreign, but is related. I am trying to make the table into address form with name row, address row and city state and zip concatenation into the report. Every example I find uses individual text boxes for this; quite ugly! Anyone out there in access land have any ideas? Thanks.

 

[scriverb]

If I read your question correctly you want the concatenation of these fields done so that it can be displayed as one control or printed as one control. By maintaining your link information you can use a query to create a new column of data such as this:

Select A.AddressID, (Trim(A.Name) & vbCrLf & Trim(A.Address) & vbCrLf & Trim(A.City) & ", " & Trim(A.State) & " " & Trim(A.ZipCode)) as NameAndAddress
FROM tblAddresses As A;

You can now use this query link to your other tables in your forms and reports and just display this single datafield called NameAndAddress. Is this what you were looking for? If not provide more info and get back with me.
Bob Scriver

 

[ramondrumon]

Thank you very much, I will play with this and I will reply to you how I do with it!!

 

[ramondrumon]

I am having trouble getting this to work and I may be putting it in the wrong place. I am putting this in the control source of a text box in a report. Is this right? Thanks.

 

[scriverb]

No that is not the intent. Your report has a Record Source which is either a Table or a Query which selects records from the table and displays them in columns of the query. This code would be the basis for that query. This is the SQL language behind the query. You would create a query with all of the fields selected that you want for your report. You would then add a new column by pasting in the part of the provided code that creates your new field:

(Trim(A.Name) & vbCrLf & Trim(A.Address) & vbCrLf & Trim(A.City) & ", " & Trim(A.State) & " " & Trim(A.ZipCode)) as NameAndAddress
Now in the report you may create a control with the Control Source as NameAndAddress from the dropdown box.

If you have further trouble with this please get back and I can be very specific about how to create and use this query in the report.
Bob Scriver