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. 10 10497
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, " ", "")
"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 . . .)
"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
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
"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
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.
"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.
"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.
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: glenn |
last post by:
I'm really scratching my head over this one. I'm working with CSV data exported
from Excel, which explains why it's a mess to begin with.
Within a table (or via any other means someone might be...
|
by: Ilan |
last post by:
Hi all
I need to add data from two Excel sheets (both on the same workbook)
to an existing table in my SQL DB.
The problem is that each sheet holds different fields for the same
record, though...
|
by: M.Stanley |
last post by:
Hi,
I'm attempting to create a query that will combine 2 columns of
numbers into one. The followng comes from 1 table with 4 fields
(A,B,C,D)
A B RESULT
700 000 700000
700 001 ...
|
by: Omey Samaroo |
last post by:
Dear Access Gurus,
Can anyone provide me with some much needed assistance.
I would like to combine the contents of 3 text fields into one field. Can
someone provide some code or a method to do...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my...
|
by: Will |
last post by:
I have a table, tblManinstructions with fields Code & InstructionID, one
Code can have many InstructionID. I also have tblinstructions (fields
instructionID & instruction). What I want to do is...
|
by: Matthew Louden |
last post by:
I want to know why C# doesnt support multiple inheritance? But why we can
inherit multiple interfaces instead? I know this is the rule, but I dont
understand why. Can anyone give me some concrete...
|
by: justin tyme |
last post by:
Hello Experts!
I would like to combine (which may not be the correct technical term) two
text fields from the same table in a query. Specifically, text field A and
text field B are both lists of...
|
by: rpeacock |
last post by:
I have a function that takes a field with values separated by commas within the field and splits them to multiple rows.
Example:
Field - Interior
Value - abc,def,efg,ghi
Output:
ID Item
1 ...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
|
by: SueHopson |
last post by:
Hi All,
I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...
| |