combining multiple address fields into 1 address field

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.

Re: combining multiple address fields into 1 address field

On Nov 27, 7:24 am, "H" wrote:
> 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, " ", "")

Re: combining multiple address fields into 1 address field

"Ben" wrote in message
news:3b856759-086f-4abf-9a77-8e48f025198a@s19g2000prg.google groups.com...
> On Nov 27, 7:24 am, "H" wrote:
> > 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 . . .)

Re: combining multiple address fields into 1 address field

"Ben" wrote in message
news:3b856759-086f-4abf-9a77-8e48f025198a@s19g2000prg.google groups.com...
> On Nov 27, 7:24 am, "H" wrote:
>> 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

Re: combining multiple address fields into 1 address field

> 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

Re: combining multiple address fields into 1 address field

"Stuart McCall" wrote in message
news:fiib13$srj$1$8300dec7@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

Re: combining multiple address fields into 1 address field

> 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.

Re: combining multiple address fields into 1 address field

"Stuart McCall" wrote in message
news:fikimb$fmd$1$8300dec7@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.

Re: combining multiple address fields into 1 address field

"H" wrote in message
news:4NOdnZDwFcHvf9DanZ2dnUVZ8sqjnZ2d@eclipse.net.uk...
>

Sorry H I didn't realise you had so little Access experience. It's sometimes
hard to judge someone's level of expertise on here. I'm a little rusty at
guiding novices, but let's give it a go and see if we can't do better
between us. Read on:

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

Here's where you need the formatted address. For display/printing (more
later).

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

Here's where you need the fields as you have them, in order to restrict
Access to getting you just the data you need and disregarding the rest.

> 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.

Correct. frm_members is said to be a bound form, because it has the name of
a table or query in its RecordSource property. In your case the table
Sheet1.

> 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.

By the sound of things I think you're right (it wouldn't do what you
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.

Yes this is a very bad idea. Data should only be stored once in any
database.

> 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.

No you don't need (or want) to do that. As I pointed out previously, you
need both the fields as you have them, plus you want a formatted date block
for printing purposes etc.

Well it turns out that Access reports (which is what you'll be printing) can
have either a table or a query as the source of their records (RecordSource
property again). If you were to make a query containing all your sheet1
fields, plus a reference to my function, you would have all you need to
supply your report with data. This means that you aren't STORING the data
twice, just displaying it differently. Although the function doesn't do a
calculation, this type of 'field' (it will show in your query just like
another field) is known as a calculated field. The data exists in one form
(your fields) and is DISPLAYED in another (the result of the function AKA
it's return value).

Anyhow, here's how to construct your query:

Create a new query and when Access asks you how you'd like to create it,
choose 'Design View'.
Access will show you a list of tables. Select Sheet1 and click the 'Ok'
button, then click the close button.
What you're looking at now is the query designer. Notice how Sheet1's fields
are listed in the top half of the window.
For each field name in the list, double click it and it will be added to the
grid in the bottom half of the window.
Now here's where you add the call to the function. Click on the next empty
grid cell to the right of the fields and type the following:

Address: =AddrLines("Address1", "Address2", "Village", "Town", "County",
"Country", Postcode")

(that should be all on one line despite how it looks here)

Switch the query designer into 'Datasheet View' (View Menu -> Datasheet
View). In order to check the rightmost column to see the formatted address
you'll have to increase the row size. You do this much the same as in Excel:
put the mouse over the division between one row and the next, then click and
drag downwards.

When you're happy that all is displayed properly, save the query and close
it.

Now you have a record source for any reports you create, which is quite a
different ball game altogether (too much to go into here).

Does that help at all?

Re: combining multiple address fields into 1 address field

"Stuart McCall" wrote in message
news:fikros$kop$1$8302bc10@news.demon.co.uk...
> "H" wrote in message
> news:4NOdnZDwFcHvf9DanZ2dnUVZ8sqjnZ2d@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.

Re: combining multiple address fields into 1 address field

> 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.
>

Re: combining multiple address fields into 1 address field

See sniped elements below

"Stuart McCall" wrote in message
news:fil4av$5i2$1$8300dec7@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.