How do i move part of a value to a new field?

[Guest]

The following is two sample records in two fields of my db which has been
imported from excel, after being scanned into a ocr program:

KEY ADDRESS
1 aby street.......7h 192
2 aby street west.....5h 84

field 1 contains the obvious.
field 2 conrtains a street/road name, some useless dots, a grid reference,
and finally a page number, of which the grid ref can be found.

What i want to do is take the grid ref, and the page no out of the ADDRESS
field and put them into their own individual fields as follows.

KEY ADDRESS GRID PAGE
1 aby street 7h 192
2 aby street west 5h 84

I have 500 records on 100 pages to sort. If anyone can help, Thanks.

 

[Guest]

The following is two sample records in two fields of my db which has been
imported from excel, after being scanned into a ocr program:

KEY ADDRESS
1 aby street.......7h 192
2 aby street west.....5h 84

field 1 contains the obvious.
field 2 conrtains a street/road name, some useless dots, a grid reference,
and finally a page number, of which the grid ref can be found.

What i want to do is take the grid ref, and the page no out of the ADDRESS
field and put them into their own individual fields as follows.

KEY ADDRESS GRID PAGE
1 aby street 7h 192
2 aby street west 5h 84

I have 500 records on 100 pages to sort. If anyone can help, Thanks.

The easiest solution is probably to split it in Excel before you import to
Access. From the Execl menu pick "Data" then select "Text To Columns" and
follow the wizard. Selected delimited, and when asked for the delimiter put
"." (without the quotes) the Other box input, and select Treat consequtive
delimiters as one. You may have to do this a couple times, the first time to
split out the dots, and then a second time to seperate the Grid and Page
information.

gm

 

[doco]

Either that or build some user defined functions (if familiar to VBA) to
parse out what you need. See help files for InStr$(), Right$(), Left$() and
Len(). You will need to find first and last position of "." and if 'h' is
always part of grid, you will need to discover what position it holds. If
alpha portion of grid is variable then you will also need to see help files
for Chr() and or Asc().

doco

 

[doco]

I found this in a previous post that you might find usefull

http://www.infoplan.com.au/splitter/

doco

 

[Tom Wickerath]

doco,

Splitter for Microsoft Access is designed for splitting names, not addresses.


Tom
______________________________________-


I found this in a previous post that you might find usefull

http://www.infoplan.com.au/splitter/

doco

 

[James Hahn]

It depends a bit on the exact details of the data. One option is to use the
global search and replace function in WORD to separate the data into
tab-delimited columns. Once in this format it can be saved as text and
imported into an ACCESS table. For instance, for your example you could
replace
^w^#^$^w
with
^t^&^t
You might have to repeat this with
^w^#^#^$^w
This would get the hours and minutes into their own columns. Finding the
end of the street address might be trickier, depending on the detail of the
.... data.

 

[Albert D. Kallal]

Hum...lets take shot at this:

strText = "aby street.......7h 192"

strAddress = split(strText,".")(0)

v = split(strText," ")

intMax = ubount(v,1)

strPage = v(intMax)
strGrid = v(intMax - 1)

Hey...I typing this message as we go.....and am thinking aloud..but the
above looks good to me!

However, if any address has a "dot", then the above will not work. However,
is it possible that EVERY address has more then one dot?

Ie: aby N. Y. street.....7h 192

In the above..there is some periods...but also a set of dots...if you can
safely answer yes to the "set" of dots, then go:

strAddress = split(strText,"..")(0)

In other words...just use spit with two ..


Ok...so lets put together some code to execute the above:

Public Sub SplitAddress()

Dim rstRec As DAO.Recordset
Dim strAd As String
Dim v As Variant
Dim intMax As Integer


Set rstRec = CurrentDb.OpenRecordset("tblLoactions")

Do While rstRec.EOF = False
' grab data after all the "dots"
strAd = Split(rstRec!Address, "..")(1)
'splti this data by a space
v = Split(strAd, " ")
rstRec.Edit
intMax = UBound(v, 1)
rstRec!Page = v(intMax) ' grab the last guy
rstRec!Grid = v(intMax - 1) ' grab the 2nd to last guy
strAd = Split(rstRec!Address, "..")(0) ' grab part beofre the
.....
rstRec!NewAddress = strAd
rstRec.Update

rstRec.MoveNext

Loop

End Sub


The above might not be perhect..and I just typed this in as air code...(I
type very fast...).

Try the above...(and course on copy of the data...). Anyway, the above
should give you some ideas.