Morning All,
I cannot find the answer to my question anywhere.
I have an address field named 'ADDRESS'. I want to run an update query so that the first two words of the address are split into a new field named 'AddressSplit'.
Therefore if the full address showed the following
'Ruby Cottage Edinburgh Scotland'
I would get 'Ruby Cottage' in new field.
Likewise if the address was '5 Aberdeen Way Glasgow Scotland'
I would get '5 Aberdeen' in the new field.
Would be grateful of any help and would prefer a query sequence rather than VBA code.
Thanks
Sharkiness
13 6809
Personally, I feel as though a simple Function, coupled with an Update Query will easily do the trick. - Function Definition:
- Public Function fSplitAddress(strAddress As String) As String
-
Dim varSplit As Variant
-
-
varSplit = Split(strAddress, " ")
-
-
'Just in case, 1 word Address
-
If UBound(varSplit) = 0 Then Exit Function
-
-
fSplitAddress = varSplit(0) & " " & varSplit(1)
-
End Function
- UPDATE Query:
- UPDATE tblAddress SET tblAddress.AddressSplit = fSplitAddress([ADDRESS])
-
WHERE tblAddress.ADDRESS Is Not Null;
- Any questions, feel free to ask.
NeoPa 32,556
Expert Mod 16PB
If you can work on the assumption that each word is separated by a single space, then you could use : - AddressSplit: Left([Address],InStr(InStr(1,[Address],' ')+1,[Address],' ')-1)
Unfortunately, most of the powerful functions ( Split(), Join(), etc) can only be used within VBA as they require reference to arrays.
This is only the version to display the value. You should manage converting that to an update without assistance.
NeoPa 32,556
Expert Mod 16PB
If you choose to use ADezii's approach (and there's no good reason not to unless performance is an issue across many hundreds of records) then you may want to adjust line #7 to : - If UBound(varSplit) = 0 Then
-
fSplitAddress = strAddress
-
Exit Function
-
End If
Currently, there appears to be no value set if there are no spaces found in the data. A simple oversight I expect.
Hello NeoPa, won't the Code in Post# 3 fail for 2-Component Addressess such as 2937 Clifford, #47 Sunset, etc.?
NeoPa 32,556
Expert Mod 16PB
Only if you ignore the assumption posted before it (which is supported by the example data given). This is a good point to bring up though, nevertheless.
If the assumption were that each word is separated by a single space, but optionally could include commas before some spaces (as ADezii suggests), then you could use : - AddressSplit: Replace(Left([Address],InStr(InStr(1,[Address],' ')+1,[Address],' ')-1),',','')
That is certainly a more standard format for addresses, I must admit.
A further point should be borne in mind. Such SQL will work from within Access, but such embedded function calls may cause trouble when accessing this query from outside of Access.
NeoPa 32,556
Expert Mod 16PB ADezii:
Hello NeoPa, won't the Code in Post# 3 fail for 2-Component Addressess such as 2937 Clifford, #47 Sunset, etc.?
I completely misunderstood your question. You mean for "2937 Clifford", "#47 Sunset", etc.
The answer is Yes. It does (fail). Let me see if I can find a more successful set of SQL for this, which handles the good point ADezii raised.
PS. Not only for two word data either. It also fails for a single word (It's quite cr*ppy really when I think about it :-D).
NeoPa 32,556
Expert Mod 16PB
It's getting a little clumsy now. That is sometimes the nature of working in SQL rather than VBA. If you're determined that it must be done outside of VBA though, the following should work : - AddressSplit: IIf((Len([Address])-Len(Replace([Address],' ','')))>1,[Address],Replace(Left([Address],InStr(InStr(1,[Address],' ')+1,[Address],' ')-1),',',''))
Any comments are, as always, welcomed. I'd rather know if it's wrong than have everyone see I do cr*ppy answers ;-)
Thanks are due to ADezii for spotting what I should have spotted before posting.
I guess that we are lucky enough to occasionally spot each other! (LOL).
NeoPa 32,556
Expert Mod 16PB
It seems it's easier to spot each other's mistakes than our own eh, my friend?
I hate to post anything with mistakes in, but if I do, at least you spot them for me :-D
Thanks guys, sorry for delay, only getting round to working on again now. Will get back to you if there are any problems. Although i doubt there will be
NeoPa 32,556
Expert Mod 16PB
No worries :-)
Let us know if you have any problems.
NeoPa 32,556
Expert Mod 16PB
I looked, but it's not something I do. I export datasets to Excel, but never data formatted by an object such as a form or report. I could never understand why anyone would want to myself, but it seems many do.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: bk_kl |
last post by:
Hi,
I think the following behavior of the build in function 'split' is inconsistent.
What do you think?
>>> "".split()
>>> "".split(";")
|
by: porterboy76 |
last post by:
QUESTION:
How do I split a directory string into a list in Python, eg.
'/foo/bar/beer/sex/cigarettes/drugs/alcohol/'
becomes
|
by: 00steve |
last post by:
Hi,
I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!) I
was wondering if anyone knew how to write a query...
|
by: happy |
last post by:
I will reissue my question . How to form and write string field to a
disk file in one statement usinf fprintf?
my struct as below :
struct record
{
char customer_name;
int customer_no;...
|
by: merrittr |
last post by:
I have a string "c:\upload\file.txt"
what I want is the file name to do this I was trying split on \ but
I cant seem to get it to work?
Server Error in '/appform' Application....
|
by: Goran |
last post by:
Can I define field names with more words in Access and SQL Server like
field: "Bus station" instead "BusStation" or "Bus_Station"? I have had
problems because of this in VB6. Can I have problems in...
|
by: dayharbor |
last post by:
'Splits full name into first/last name fields
Private Sub SplitNames()
Dim rsNames As DAO.Recordset
Dim SplitName As String
'split name field
strSQL = "SELECT fldName FROM...
|
by: schandra60 |
last post by:
I have a 'Names' field in a table which has names with initials like 'T S Rxxx', 'T.S.Rxxxx', 'TS Rxxx'.
But if the user types 'T S R' or 'T.S.R' in search box, the result should show all these...
|
by: KIRAN |
last post by:
Hi all,
can i split a C string like this?
char * p = "Hello \
World\n\r";
is this according to standard?
Any help or link to standard regarding the above doubt is
aprreciated...
Regards,
Kiran
|
by: HSXWillH |
last post by:
I have a table entitled Stock_Catalog. Fields include Stock_ID (random autonumber), Brand, Card_Number and Player_Name. Most of the entries have 1 name in the Player_Name field, but some show...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |