Seperating barcode information?

[escudolm]

Hello all, I am trying to make it so when I scan a barcode, it goes into a table I created in Acccess all nice and neat. I got a Wasp barcode reader and I have it set up, so when I scan something it goes into a table I created in Access named tbllicense. But when I scan it, it does one long line of info into only one field instead of putting the info into seperate fields. Here is an example of what I mean:

@
ANSI 6360050101DL00300207DLDAQ004435531DAASMITH,MATT,S,


This is what it looks like when I scan the barcode. The numbers 004435531 are the first numbers I need to get put into a certain field. Then I need Smith for the last name, Matt for the first, and S for the middle and so on. How would I go about this? I was thinking of maybe telling it to pull the 9 digits after "DAQ". Or should I try to use another method? And for the last name, kinda the same way, pull the text from after "DAA" to the comma. Any advice would be great. Thanks.


PS. Also put this in Office Development. Sorry if this is in the wrong forum.
Trying to do the VB coding in Access, if you didn't already know.

[Static]

how are you adding the name after the bar scan?

is the "code" always the same length??

you need to somehow put a delimeter in between each section

[escudolm]

The name gets printed out when I do the barcode scan, if thats what you mean. Its part of the info on the barcode (name, birthday, address, etc.) The code I believe is always the same length, should be. Sorry, but whats a delimeter and how would I go about putting it in the code. Thanks.

[Hack]

Trying to do the VB coding in Access, if you didn't already know.

Based on this comment Moved to Office Development with existing responses.

[Static]

Delimeter is a a seperator (like 1,2,3,4,5 ) , is the delim...

so when you scan the barcode.. thats what come out.. you arent adding anything

[RobDog888]

A barcode generally needs a start and stop character for the beginning and end of the BC. If you place a deliminator like a space, you could split upon the space as a way to break up the code and enter into different fields. Note: to enter a space character its not usually a space character. You need a special character to represent the space or the BC wont be scannable.

[escudolm]

Well, I guess I should be more specific. I am scanning the back of Drivers Licenses so the info from the code on the back goes into a table with all their info (number, name, address, etc.). Its for a charity run. They want to scan the licenses to have all the info on the person and swip a credit card to get payment. Then got to match up the info for both. So, this deliminator should be able to seperate the info from the barcode line that I need to put it into the right field in my table?

[RobDog888]

Oh, so then maybe the drivers license would already contain so way of delimiting it since they do it already?

[escudolm]

Well, I'll show you what it looks like when it gets scanned:

@
ANSI 6360050101DL00300207DLDAQ114435531DAASMITH,MATT,S,DAG123 FAKE DRDAIMOUNT PLEASANTDAJSCDAK294643741 DARDM DAS DAT DAU602DAW175DAY DAZ DBA20080501DBB19710501DBC1DBD20030823DBG2DBH2

This is what it looks like. Well the info is changed to protect privacy, but still the same. It list the license # after DAQ and the name after DAA. Then the address after DAG, state after DAJ, zip after DAK, class after DAR, height after DAU, weight after DAW, and the issued/exp. date in the last line. That is what I'm dealing with. So I am just trying to seperate the info into the fields for the table in Access. And all of the scans aren't the same length like I thought, due to diff. addresses.

[RobDog888]

All of the scans should be different lengths, no? Different length names, addresses, etc.

But it looks like its easiest to just scan in the data and process it and then write it out to its respective fields then

[escudolm]

So you mean to just scan it and then type each data for the certain fields in by keyboard? And yeah about the diff. lengths, all that good stuff. Thanks for all the help so far also.

[RobDog888]

You can scan it into a textbox and then, maybe just to get it working, create a command button to do the splitting and entering of data. Scan and the data goes into TextBox1. Then click Command1 and that will parse out the data in TextBox1 and insert the split data into a record. Use Instr, Mid$ and Len to parse the data.

[escudolm]

Thanks RobDog. I really appreciate the help. I'm gonna try right now. I'll let you know if I get it. I don't want to put up a resolved yet. Thanks.

[RobDog888]

No prob. If you have any more questions or need more help on this post away

[Webtest]

Boy, are you going to have fun parsing this thing when you get to DAGWOOD BUMSTEAD!

[RobDog888]

Naw, shouldnt be impossible. The driveers license is within a certain range in the beginning and the number is static in length and surrounded by the "DL00300207DL" tags. You could just do the same with the others. Look in a given range for them and make sure the next position is the expected data. Like "DAG" comes 2 chars after the comma and middle initial.

[Webtest]

Try this one (if the Dept. of Motor Vehicles/Transportation/etc. doesn't foul it up first!):

First Name: DAGWOOD
Middle Initial: (none)
Last Name: VAN DAMME
Suffix: SR.

Isn't this great fun!!!

[RobDog888]

It still wouldnt be that har since Instr returns the first instance of a siring in a string so it would find the tag "DAG" and then the next data would be the name DAGWOOD

[escudolm]

Alright, I tried parsing it out the data after it was scanned into labels but that didn't work. Tried using the left(strString) to grab the numbers, but to no hope. Could you possibly throw out an example of maybe an easy way to parse the data? Here's that example again, just incase:

@
ANSI 6360050101DL00300207DLDAQ114435531DAASMITH,MATT,S,DAG123 FAKE DRDAIMOUNT PLEASANTDAJSCDAK294643741 DARDM DAS DAT DAU602DAW175DAY DAZ DBA20080501DBB19710501DBC1DBD20030823DBG2DBH2

The lic. number is 11443553. Thanks.

[Webtest]

It still wouldnt be that hard since Instr returns the first instance of a string in a string so it would find the tag "DAG" and then the next data would be the name DAGWOOD

The DAG Tag is a prefix for the Address and comes right after the first name, doesn't it? The first 'DAG' you'll find for DAGWOOD is not the DAG tag, but a name! That's exactly what came to mind as I was sitting here reading the comics!

[DKenny]

An incomplete, but possible helpful, list of Field Ids and associated data elements...

ID Field
DAA Name
DAG Street Address 1
DAI City
DAJ Jurisdictional Code
DAK Postal Code
DAQ License/ID number
DAR License Classification Code
DAS License Restriction Code
DAT License Endorsement Code
DAU Driver Height
DBA License Expiration Date
DBB DOB
DBC Sex
DBD Issue Date

[Webtest]

You have a series of numeral digits ... is it always 10 digits?

Then you have the string "DLnnnnnnnnDL" where "n" is always a numeral ... are there always exactly 8?

Then you have the string "DAQnnnnnnnnn" where "n" is always a numeral ... are there always exactly 9? The first non-numeral starts the next field.

Now comes the fun part! You have a string containing 3 fields which presumably always starts with "DAA", and always ends with the 8 alpha character string "DAJxxDAK" (where xx is always a 2 alpha char State). This string will always have at least some characters followed by one or more occurrences of "DAG", and then some more characters followed by one or more occurrences of "DAI". The "or more" factor is the killer. If there is only one occurrence, it is obviously the TAG. I guess the solution might be ... check for more than ONE occurrence of the DAG and DAI tags ... if there is only one of each, you are home free. If there is more than one of either, make the dude register on paper! Does that sound workable?

[DKenny]

OK, now I've gone way too deep into this...

If your feeling really bored some night.
http://www.aamva.org/Documents/std20...pecV2FINAL.pdf

[Webtest]

An incomplete, but possible helpful, list of Field Ids and associated data elements...

ID Field
DAA Name
DAG Street Address 1
DAI City
DAJ Jurisdictional Code
DAK Postal Code
DAQ License/ID number
DAR License Classification Code
DAS License Restriction Code
DAT License Endorsement Code
DAU Driver Height
DBA License Expiration Date
DBB DOB
DBC Sex
DBD Issue Date

What happened to the State? Evidently fields without data can be omitted (as in DAJ Jurisdictional Code)?

[DKenny]

DAJ Jurisdictional Code = State
I think they realize that there are more than one country in the world...

[RobDog888]

I remember that for California the DAJ code is 31 and I am surprised to see character data as the DAJ - "SC" unless is hex for a global id number.

Ps, Nope, I think they went overboard writting up a 66 page document on the format of an id card.

[escudolm]

Sorry, the license number is always 9. Well at least all that I have seen is. I believe there is only one occurence of DAG and DAI in the line. Still trying to figure this one out. All the help/tips so far are greatly apprciated.

[Webtest]

Here is an Excel Macro to play with - the source string is in cell $A$1:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/13/2005 by guawd1
Dim strX As String
Dim str0 As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4 As String
Dim str5 As String
Dim str6 As String
Dim str7 As String
Dim i As Integer
Dim j As Integer
'
    strX = Range("$A$1").Value
    str0 = ""
    MsgBox strX
    str0 = Left$(strX, 1)
    While Not IsNumeric(str0)
        str1 = str1 & str0
        strX = Mid$(strX, 2)
        str0 = Left$(strX, 1)
    Wend
    MsgBox str1
    str2 = ""
    While IsNumeric(str0)
        str2 = str2 & str0
        strX = Mid$(strX, 2)
        str0 = Left$(strX, 1)
    Wend
    MsgBox str2
    str0 = Left$(strX, 2)
    If str0 <> "DL" Then GoTo BAD_PARSE
    strX = Mid$(strX, 3)
    str3 = ""
    str0 = Left$(strX, 1)
    While IsNumeric(str0)
        str3 = str3 & str0
        strX = Mid$(strX, 2)
        str0 = Left$(strX, 1)
    Wend
    str0 = Left$(strX, 2)
    If str0 <> "DL" Then GoTo BAD_PARSE
    strX = Mid$(strX, 3)
    MsgBox "DL|" & str3 & "|DL"
    str0 = Left$(strX, 3)
    strX = Mid$(strX, 4)
    If str0 <> "DAQ" Then GoTo BAD_PARSE
    str4 = ""
    str0 = Left$(strX, 1)
    While IsNumeric(str0)
        str4 = str4 & str0
        strX = Mid$(strX, 2)
        str0 = Left$(strX, 1)
    Wend
    MsgBox "DAQ|" & str4
    str0 = Left$(strX, 3)
    strX = Mid$(strX, 4)
    If str0 <> "DAA" Then GoTo BAD_PARSE
    
    'HERE IS THE FUN PART!  I picked "6" as the ABSOLUTE minimum characters for the name field
    i = InStr(6, strX, "DAG")
    If i < 1 Then GoTo BAD_PARSE
    j = InStr(6 + i, strX, "DAG") 'Are there any more DAGs?
    If j > 0 Then GoTo BAD_PARSE 'More than 1 "DAG" aborts
    str0 = Left$(strX, i - 1)
    strX = Mid$(strX, i + 3)
    MsgBox "DAA|" & str0
    
    i = InStr(6, strX, "DAI")
    If i < 1 Then GoTo BAD_PARSE
    j = InStr(6 + i, strX, "DAI") 'Are there any more DAIs?
    If j > 0 Then GoTo BAD_PARSE 'More than 1 "DAI" aborts
    str0 = Left$(strX, i - 1)
    strX = Mid$(strX, i + 3)
    MsgBox "DAI|" & str0
    
    i = InStr(6, strX, "DAJ")
    If i < 1 Then GoTo BAD_PARSE
    j = InStr(6 + i, strX, "DAJ") 'Are there any more DAJs?
    If j > 0 Then GoTo BAD_PARSE 'More than 1 "DAJ" aborts
    str0 = Left$(strX, i - 1)
    strX = Mid$(strX, i + 3)
    MsgBox "DAJ|" & str0
    
    'etc.
    
    MsgBox strX
    
    Exit Sub
    
BAD_PARSE:
    MsgBox "I can't Parse this junk"
'
End Sub

Notice that DAGWOOD will get thrown out, as will anyone living on DAISY DR or anyone living in a city containing "DAJ". I'm in a hurry to get home, so forgive my lack of comments. My code ALWAYS has at least as many lines of comments as lines of code.

[escudolm]

Wow, thanks WebTest for the info. Appreciate it. Just got to work, so I am about to start trying to tackle this thing again. Let you guys know.

[Webtest]

The Tags that are being used STINK. None of them are even close to unique. DAG is a fairly common Scandanavian first name; it is found in several cities: Daggett, Dagenham; and you could probably find some streets with "DAG" in the name. I imagine DAI is about the same ... Daisy could be in a name, a street, and a city, all for the same person!

[escudolm]

I wonder if I could check for the first DAG or DAI and then pull the information that is after it. Like scanning for DAG then pulling the info until it reaches DAI and so on. Might try to put that in somehow.

[Webtest]

I wonder if I could check for the first DAG or DAI and then pull the information that is after it. Like scanning for DAG then pulling the info until it reaches DAI and so on. Might try to put that in somehow.

The first "DAG" you find could be a part of the First Name, it could be a part of the Last Name, or it could be the DAG Tag. How can you tell? You can't cut off the search for "DAG" with "DAI" because you might find "DAG" in the First Name and "DAI" in the Last Name (or whatever), but you still haven't gotten to the real DAG Tag. What the dickens do you do then? It is a mess, and sorry, but I'm done with it. You have fun! It's giving me a headache.

[escudolm]

I got what your saying. Never thought about it that way. Wow, your right, this is gonna suck. Thanks anyway.

[RobDog888]

Well, the government must have some way to do it since they created it and are using it.

[Webtest]

Well, the government must have some way to do it since they created it and are using it.

... are using it??? Does the patrol officer scan the code on your card when he pulls you over? (I wouldn't know ... I've never been pulled over). I know the clerk at the office doesn't scan your card ... they just type in your SSN!!! Has anybody ever seen the bar code used officially? It's probably just there as absolute proof that your tax dollars are not being wasted!

[RobDog888]

Yes, at the big drug store chain Savon Drugs they swipe your DL when you write a check.

[Webtest]

The answer is in the document that was graciously provided by DKenny in post #23. The data you are getting is the 'stringified' data which has thoughtfully removed all of the obnoxious field/record/segment terminators/separators! That is a function of the software/firmware that is being used to translate the PDF417 'barcode' to the text you are seeing. What you really need is the raw decoded data complete with all special marks, such as Line Feeds, Carriage Returns, and 'record separator' characters. Take a look at section D of the spec.

It's sort of like the difference between looking at an HTML web page through your browser, or looking at it in NotePad. BIG difference!

[RobDog888]

How true and what a great observation. Just goes to show you how a document will not be read when its intense and bloated.

[DKenny]

I can lead a horse to water but he's got to buy the beers.. ;-)

[Webtest]

Can you load the data string into a BYTE ARRAY instead of a String? If you can do that, you might preserve the special characters. Then you can change the yucky bytes to unique printing characters that you can handle with string functions.

Tell your Horse friend to make mine Shiner Bock or Celis White.