Separating a string into various fields

[Guest]

I need to separate a single string into 5 Different Fields...

If someone helps me by giving me code for this, can they also help me, with
having the code apply itself to the whole recordset... cause for example, I
know how to use the Left function and what not to extract a single record, by
making a form with textboxes, and putting the following into the OnCurrent
event, this method, however, I have to scroll down 250,000 records... very
time consuming...
-------------------------------------------------------------------------------------
Dim strCode, strName, strSex, strDate, strDateFixed, strMo, strDa, strYe As
String
Dim intCounty, intID As Integer
Dim dDate As Date

'separates everything

strName = Left(strCode, 6)
strDate = Mid(strCode, 7, 6)
intCounty = Mid(strCode, 13, 2)
strSex = Mid(strCode, 15, 1)
intID = Right(strCode, 3)

'fix date format since it was in yy/mm/dd
strYe = Left(strDate, 2)
strMo = Mid(strDate, 3, 2)
strDa = Right(strDate, 2)
strDateFixed = strMo & "/" & strDa & "/" & strYe
dDate = strDateFixed

'sets it back to the control on the form
Me.CVE_NOM = strNombre
Me.CVE_DOB = dFecha
Me.CVE_EDO = intEdo
Me.CVE_GEN = strSexo
Me.CVE_ID = intID

Me.Repain

 

[Rick B]

Are you trying to separate these items into separate fields into your table
and then delete the combined field? In other words, are you correcting a
flaw in your original deisgn?

If so, just create the five new fields in your tabel, then build an update
query to update each field with the new value. When you run it, it will
update each record and will most likely take less than a minute or two.

Rick B

 

[Guest]

yes... I got this table like this... and it has a whole bunch of desing
flaws... its really hard to get info out of it....

how do I do this Update Query... specific instructions would be nice...
since I am getting the info out of a single String, that has all of the
info....

 

[Rick B]

Make a backup of your table first!!!



Add the various new (blank) fields to your update query. Underneath the
first new field (I don't know what it is called in your table) put the
following in the "Update to:" square...

=Left([SomeFieldName], 6)


Replace "SomeFieldName" with the name of your large combined field.


Use similar statements for each of the other fields and then run it.

Rick B

yes... I got this table like this... and it has a whole bunch of desing
flaws... its really hard to get info out of it....

how do I do this Update Query... specific instructions would be nice...
since I am getting the info out of a single String, that has all of the
info....

Are you trying to separate these items into separate fields into your table
and then delete the combined field? In other words, are you correcting a
flaw in your original deisgn?

If so, just create the five new fields in your tabel, then build an update
query to update each field with the new value. When you run it, it will
update each record and will most likely take less than a minute or two.

Rick B

I need to separate a single string into 5 Different Fields...

If someone helps me by giving me code for this, can they also help me, with
having the code apply itself to the whole recordset... cause for

example,
I

know how to use the Left function and what not to extract a single

record,
by

making a form with textboxes, and putting the following into the OnCurrent
event, this method, however, I have to scroll down 250,000 records... very
time consuming....

-------------------------------------------------------------------------- strYe
As

String
Dim intCounty, intID As Integer
Dim dDate As Date

'separates everything

strName = Left(strCode, 6)
strDate = Mid(strCode, 7, 6)
intCounty = Mid(strCode, 13, 2)
strSex = Mid(strCode, 15, 1)
intID = Right(strCode, 3)

'fix date format since it was in yy/mm/dd
strYe = Left(strDate, 2)
strMo = Mid(strDate, 3, 2)
strDa = Right(strDate, 2)
strDateFixed = strMo & "/" & strDa & "/" & strYe
dDate = strDateFixed

'sets it back to the control on the form
Me.CVE_NOM = strNombre
Me.CVE_DOB = dFecha
Me.CVE_EDO = intEdo
Me.CVE_GEN = strSexo
Me.CVE_ID = intID

Me.Repaint