combining multiple address fields into 1 address field

H

Hi,

I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country

Since I can't make all fields mandatory, I'd like to create a way of
capturing only the data in fields that have data and putting that in another
field called 'address' so that field reads conventionally (i.e each fields
is on a separate line - and for lines to be moved up if no data is in the
preceding field.)

I had thought of making an update query using IIf statements but I can't see
what character to use for 'carriage return'.

Any help would be much appreciated.

Regards,

Hal.

Nov 27 '07 #1

Ben

On Nov 27, 7:24 am, "H" <h...@oldspacewestern.co.ukwrote:

Hi,

I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country

Since I can't make all fields mandatory, I'd like to create a way of
capturing only the data in fields that have data and putting that in another
field called 'address' so that field reads conventionally (i.e each fields
is on a separate line - and for lines to be moved up if no data is in the
preceding field.)

I had thought of making an update query using IIf statements but I can't see
what character to use for 'carriage return'.

Any help would be much appreciated.

Regards,

Hal.

It may be better to do this with a query to view the data, rather then
adding another field.

Either way you are probably looking for a way to concatenate the
fields together. It goes something like this

Select flat_number & " " & house_name_or_number & " " & street . . .

Notice the added spaces between the fields, '& " " &', this keeps the
words separated by a space. However, if you are missing a field or two
then you will get double spaces within the string. It is probably
easiest to just build the string with the double spaces and then clean
it up afterwards with a Replace function:

Select replace(flat_number & " " & house_name_or_number & " " &
street, " ", "")

Nov 27 '07 #2

paii, Ron

"Ben" <bm******@gmail.comwrote in message
news:3b**********************************@s19g2000 prg.googlegroups.com...

On Nov 27, 7:24 am, "H" <h...@oldspacewestern.co.ukwrote:

Hi,

I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country

Since I can't make all fields mandatory, I'd like to create a way of
capturing only the data in fields that have data and putting that in

another

field called 'address' so that field reads conventionally (i.e each

fields

is on a separate line - and for lines to be moved up if no data is in

the

preceding field.)

I had thought of making an update query using IIf statements but I can't

see

what character to use for 'carriage return'.

Any help would be much appreciated.

Regards,

Hal.

It may be better to do this with a query to view the data, rather then
adding another field.

Either way you are probably looking for a way to concatenate the
fields together. It goes something like this

Select flat_number & " " & house_name_or_number & " " & street . . .

Notice the added spaces between the fields, '& " " &', this keeps the
words separated by a space. However, if you are missing a field or two
then you will get double spaces within the string. It is probably
easiest to just build the string with the double spaces and then clean
it up afterwards with a Replace function:

Select replace(flat_number & " " & house_name_or_number & " " &
street, " ", "")

Strategically replacing & with + will eliminate the extra space where the
field is null.
string + NULL returns NULL
string & NULL returns string

Select flat_number & (" " + house_name_or_number) & (" " + street . . .)

Nov 27 '07 #3

H

"Ben" <bm******@gmail.comwrote in message
news:3b**********************************@s19g2000 prg.googlegroups.com...

On Nov 27, 7:24 am, "H" <h...@oldspacewestern.co.ukwrote:

>Hi,

I have the following address fields in a table:
flat_number
house_name_or_number
street
village
postal_town
county
postcode
country

Since I can't make all fields mandatory, I'd like to create a way of
capturing only the data in fields that have data and putting that in
another
field called 'address' so that field reads conventionally (i.e each
fields
is on a separate line - and for lines to be moved up if no data is in the
preceding field.)

I had thought of making an update query using IIf statements but I can't
see
what character to use for 'carriage return'.

Any help would be much appreciated.

Regards,

Hal.

It may be better to do this with a query to view the data, rather then
adding another field.

Either way you are probably looking for a way to concatenate the
fields together. It goes something like this

Select flat_number & " " & house_name_or_number & " " & street . . .

Notice the added spaces between the fields, '& " " &', this keeps the
words separated by a space. However, if you are missing a field or two
then you will get double spaces within the string. It is probably
easiest to just build the string with the double spaces and then clean
it up afterwards with a Replace function:

Select replace(flat_number & " " & house_name_or_number & " " &
street, " ", "")

Thanks for the input.

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

Nov 27 '07 #4

Stuart McCall

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

Nov 28 '07 #5

H

"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fi*******************@news.demon.co.uk...

>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

Nov 28 '07 #6

Stuart McCall

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

Ok, first of all you should rename that module, or sooner or later Access
will give you an 'ambiguous name detected' error. Call it say modAddrLines.
Anything so long as it differs from the name of the function.

I'll assume you want this combined address to go into a Form's control
(let's call it txtAddrLines). The form needs to be bound to table Sheet1 (it
should have Sheet1 in it's RecordSource property).

Here's how it's done:

Me.txtAddrLines = AddrLines(Address1, Address2, Village)

You fill in the rest of the fields the same way I've just shown. Open the
form and check it out.

I hope that helps.

Nov 28 '07 #7

H

"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fi*******************@news.demon.co.uk...

>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

Ok, first of all you should rename that module, or sooner or later Access
will give you an 'ambiguous name detected' error. Call it say
modAddrLines. Anything so long as it differs from the name of the
function.

I'll assume you want this combined address to go into a Form's control
(let's call it txtAddrLines). The form needs to be bound to table Sheet1
(it should have Sheet1 in it's RecordSource property).

Here's how it's done:

Me.txtAddrLines = AddrLines(Address1, Address2, Village)

You fill in the rest of the fields the same way I've just shown. Open the
form and check it out.

I hope that helps.

Well, to be honest - mostly no.

It might help if I explain that this DB to hold membership information of a
charitable small integrated abilities sailing club that wishes to expand.
The information will be used, amongst many other things, to create:-

- printed directories containing all contact information in an easily
readable format

- mailings, directed at specific geographic areas, vis: county, country

I previously created a form called: frm_members, which uses the table:
Sheet1, which I assume is what you mean by "bound to". The form is used to
enter data.

I don't understand what the Me. stands for in the code - nor was I sure
where to paste the code example. So, I opened the properties for the form:
frm_members but I can't see where I should paste this code. But then again
I'm not sure it would do what I intended.

My original idea was to create an update query contain a code such as you
wrote that would combine the address data for each record and update that
into a single field in an update table called: mktbl-table.

The mktbl-table table would be the source table for forms and reports, in
fact everything.

However, having written this, it seems I would be doubling (or more) the
information storage - by having 2 tables, the original and the updated
table.
So I guess I probably need a table into which 'new' or 'edited' information
is entered (called say, tbl-newinfo), upon which an update query uses that
to update the table called: mktbl-table, after which a routine runs to
delete the information in tbl-newinfo.

Does this make any more sense to you?

Regards,
Hal.

Nov 28 '07 #8

H

"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fi*******************@news.demon.co.uk...

"H" <ha*@oldspacewestern.co.ukwrote in message
news:4N******************************@eclipse.net. uk...

>>

snip

Does that help at all?

Yes! Hooray!!

I had a little problem, in that the query returned just the field names -
not the data - in the new 'Address' field of the new query, named:
qsl-4reports.

I looked at the SQL statement and removed the " marks from around the field
names, ran qsl-4reports again and hey presto - it worked!

I checked it out in reports and it works fine there too, as it should.

Mind you, I'm still puzzled by the term/function "Me." and I can't find a
reference anywhere to it. Can you illuminate?

Very many thanks for this excellent Access 101 lesson. I really appreciate
the effort and time you gave me - and the phenomenally quick response too!

Cheers,

Hal.

Nov 29 '07 #9

Stuart McCall

I had a little problem, in that the query returned just the field names -

not the data - in the new 'Address' field of the new query, named:
qsl-4reports.

I looked at the SQL statement and removed the " marks from around the
field names, ran qsl-4reports again and hey presto - it worked!

My bad. I'm always making that mistake. Seems to be a blind spot for me.
Luckily it's not something that will do any harm. It just means I'm forever
having to correct it. Ho hum.

I checked it out in reports and it works fine there too, as it should.

Mind you, I'm still puzzled by the term/function "Me." and I can't find a
reference anywhere to it. Can you illuminate?

Sure. The piece of code I posted was intended to be run in the form's class
module. Every form and report in Access can have an associated class module
where VBA code can be run in response to 'events' which occur on the form.
Things like the user clicking a button or pressing the tab key. I'll not
swamp you with info because I'm not sure you're ready for coding.

Anyway, Me refers to the form's class module. You can think of it as
referring to the form itself, because a form (or report) is just a class
module with a pretty face. This may all be running before you can walk,
though. Suffice it to say that using Me is useful because of an Access
feature called intellisense. When you type Me. (notice the period), Access
will show a dropdown list containing properties and methods in the context
of the form. So say you have a textbox called txtSomething, when the list
appears you can just type txtS and the proper control name will likely be
selected. You then press tab or enter and Access completes the word for you.

Very many thanks for this excellent Access 101 lesson. I really appreciate
the effort and time you gave me - and the phenomenally quick response too!

No problem. Glad you got it to work (and hopefully learned something - which
is partly why we're all here).

Cheers,

Hal.

Nov 29 '07 #10

H

See sniped elements below

"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fi*******************@news.demon.co.uk...

My bad. I'm always making that mistake. Seems to be a blind spot for me.
Luckily it's not something that will do any harm. It just means I'm
forever having to correct it. Ho hum.

You good!

Anyway, Me refers to the form's class module. You can think of it as
referring to the form itself, because a form (or report) is just a class
module with a pretty face. This may all be running before you can walk,
though.

in my case - crawl, slowly and painfully!

Suffice it to say that using Me is useful because of an Access feature
called intellisense. When you type Me. (notice the period), Access will
show a dropdown list containing properties and methods in the context of
the form. So say you have a textbox called txtSomething, when the list
appears you can just type txtS and the proper control name will likely be
selected. You then press tab or enter and Access completes the word for
you.

Me.txtHeadache (asprin,paracetamol,nurophen)

No problem. Glad you got it to work (and hopefully learned something -
which is partly why we're all here).

Thanks - so am I - I certainly did - you've shattered my illusion, I thought
we were all here to sail and have fun! Mind you I did here someone say we're
here just to pay tax ;-)

Hal.

Nov 29 '07 #11