VB code to separate text into proper fields 1

[Guest_imported]

I need to know how to separate text such as:

%TXPORTER^ROBINS$BRAD$ALAN^24040 LOOP 191^?
;63601504981253=040219810203?
%" 773650000 C M509175BROHZL ;B %>_ ?

which is the data found on the magnetic strip of a drivers license.

 

[THoey]

Depending on the version of Access, look up LEFT, RIGHT, INSTR, and maybe SPLIT (A2K?) in the help... Terry M. Hoey

 

[MichaelRed]

Further, you (or anyone "decoding" this) would need to know the field deffinitions, as in delimiter(s), type (Ahpha, Numeric, Date), and (aparently) widths.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Guest_imported]

well width will vary on the alphanumeric fields with the exception of state i.e. "TX"

obviously "Brett" and "Michael" are not the same char width

 

[Guest_imported]

by the way...its access 2002

 

[THoey]

But, if you look up those commands in HELP that I listed, you will see that you could use the INSTR function to find the ^ character that appears to be your delimeter. Then, you could use the LEFT, RIGHT and MID functions to grab the data chunk by chunk... Terry M. Hoey

 

[Guest_imported]

sorry. Not trying to be a jerk. I already attempted to look up those commands in help and found nothing similar.

 

[THoey]

I would import this whole data string into a field in a staging table. Then, in the OnClick event of button, I would run some code to read a record from the staging table, parse it and then store it in your separate fields of your data table. I am assuming that the TX is Texas and that PORTER is the city. If I am wrong, you should still be able to get the idea. To parse it, it should be something like:

Public Function ParseDL()

Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim WholeRecord As String
Dim LicState As String
Dim LicCity As String
Dim LName As String
Dim FName As String
Dim MName As String
Dim Address1 As String
Dim pos As Integer

Set db = CurrentDb
Set rst1 = db.OpenRecordset("select * from tblStaging&quot
Set rst2 = db.OpenRecordset("select * from tblDL&quot

rst1.MoveFirst

Do While Not rst1.EOF

rst1.Edit
rst2.AddNew

WholeRecord = rst1!DLInfo
pos = 2

rst2!LicState = MID(WholeRecord, pos, 2)
pos = 4

rst2!LicCity = MID(WholeRecord, pos, INSTR(1, WholeRecord, "^&quot - 1)
pos = INSTR(1, WholeRecord, "^&quot + 1

rst2!LName = MID(WholeRecord, pos, INSTR(pos, WholeRecord, "$&quot - 1)
pos = INSTR(pos, WholeRecord, "$&quot + 1

... *** Continue parsing different fields here. You should have the idea by now... ***

rst2.Update
rst1.MoveNext

Loop

rst1.Close
rst2.Close
db.Close

End Function

I didn't test any of this, and all of the table/field names will need to be changed to your names...

Hope that helps... Terry M. Hoey