Re: combining multiple address fields into 1 address field

"Stuart McCall" <smccall@xxxxxxxxxxxxxxx> wrote in message
news:fiib13$srj$1$8300dec7@xxxxxxxxxxxxxxxxxxx
I have already created such a query but I really wanted the addresses to
be displayed within a field in just the same way that they would normally
be written; i.e. if all the fields are used an address would appear thus:

Flat 1,
The Smart House
Cred Street
Little Village
Big Town
Countyshire
AB1 2CD
Scotland

Or, if only a few fields contain data it would be displayed so...

Smart House
Cred Street
Big Town
AB1 2CD
Scotland

This would make creating reports for address / phone books or mail merges
much easier as I would only ever had to call 1 field, instead of writing
the expression each time.

I've got as far as creating a a single line of merged text separated by a
comma and space - but I can't see how to insert a carriage return?

Regards Hal

Hal

PMFJI. The way to insert a carriage return is to use the VBA constant
vbCrLf.

This will probably help. Paste the following function into a standard
module:

''' CODE START '''
Public Function AddrLines(ParamArray adLine()) As String
Dim v, r As String
'
For Each v In adLine
If Len(v & "") > 0 Then
r = r & v & vbCrLf
End If
Next
AddrLines = r
End Function
''' CODE END '''

Use it like this:

FullAddress = AddrLines([Field Name 1], [Field Name 2]) etc. etc.

or you can use it in a query:

SELECT AddrLines([Field Name 1], [Field Name 2]) As FullAddress From
whatever


Thanks very much for your help. Being a total beginner I don't clearly
understand how I should create a module or how to write the SELECT query. I
have copied and pasted your code into a module page and saved it named as
"AddrLines".

I'd be most obliged if you could give a worked example using the field names
below:

Address1
Address2
Village
Town
County
Country
Postcode

from a table named: Sheet1


.

Relevant Pages

  • Re: Filtering "A" An" "The"
    ... Public Function TrimArticle(ByVal strInput As String) As String ... and a query ... ... GlobalSign digital certificate is a forgery and should be deleted without ...
    (microsoft.public.access.forms)
  • Re: combining multiple address fields into 1 address field
    ... Public Function AddrLines(ParamArray adLine()) As String ... r = r & v & vbCrLf ...
    (comp.databases.ms-access)
  • Re: Datatable
    ... Public Function GetRecordSet(ByVal query As String, ByVal tab As String) As DataTable ... Public Function ExecuteQueryAs DataTable ... Dim myConnection As SqlConnection = New SqlConnection ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Question Re: InStr or other function
    ... Public Function countWords(strToLookIn As String, strToFind As String) ... Unfortunately, my PC crashed and I cannot find the query that I used, so I ...
    (microsoft.public.access.queries)
  • Re: creating a compound iff statement...
    ... >>> Is it possible to create a compound IF Statement in Access Query? ... Switch and (in a public function) Select Case. ... Enter SomeName in the Name box. ... Public Function SomeName(StringIn as string) as String ...
    (microsoft.public.access.queries)