separate data from single field Access '03 Sp3

Topic

New Reply

I recall reading about these procedures but can’t seem to locate anything close enough in the archives… I need to perform the following tasks…

I have a credit card reader that puts data into a single field. 3 standard bits of information…

[CardField1] = %B123456789101112^SMITH/JOHN ^12061010000000000

Some observations:

regardless of type, all numbers are preceeded by a % and some single letter and end with a ^ and the length may vary.

next follows the lastname/first name beginning and ending with a ^ (I don’t care about trailing extra spaces)

following the last ^ is a varying length string of numbers, the first four of which are the expiration year and month.

What I need to accomplish:

separate out all the various bits of data so I can concatenate them as necessary based upon the report needed.

specifically I need:

the entire card number
the last 4 digits of the card number
first name
last name
expiration year
expiration month

I do not recall how to write the expressions necessary.

Currently, there is no real time card processing, it simply reads and stores card data in a single field. I have to parse it out later via reports and send on to the appropriate departments. If you can point me in the direction of existing code that could be run at swipe time to input the data into say 3 fields. I’m thinking some procdure like this: read between the first letter and ^#1, insert into field 1, move to field 2, read from ^#1 to ^#2 and insert into field 2, move to field 3, read from ^#2 and insert the first 4 numbers into field 3.

Any direction or referral on this would be helpful and much appreciated… Thanks… Jenn

Reply | Quote

Replies

I don’t have time for a complete solution right now, but I’d use some VBA to split the string. It would be possible to do it using expressions only, but it’d get hairy.
Here is some pseudo code. In reality, you’d supply CardField1 from the table, not as a variable.

Dim CardField1 As String
‘ Example
CardField1 = “%B123456789101112^SMITH/JOHN ^12061010000000000”
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim CardNum As String
Dim LastName As String
Dim FirstName As String
Dim ExpMonth As Integer
Dim ExpYear As Integer
a = InStr(CardField1, “^”)
CardNum = Mid(CardField1, 3, a – 3)
b = InStr(CardField1, “/”)
LastName = Mid(CardField1, a + 1, b – a – 1)
c = InStr(b + 1, CardField1, “^”)
FirstName = Trim(Mid(CardField1, b + 1, c – b – 1))
ExpMonth = Mid(CardField1, c + 1, 2)
ExpYear = Mid(CardField1, c + 3, 2)

I hope you can use this as a starting point.

Reply | Quote

Thanks Hans. I’m going to start working with this and see how far I get… Appreciate your guidance. I’ll probably be back!

I don’t have time for a complete solution right now, but I’d use some VBA to split the string. It would be possible to do it using expressions only, but it’d get hairy.
Here is some pseudo code. In reality, you’d supply CardField1 from the table, not as a variable.

Dim CardField1 As String
‘ Example
CardField1 = “%B123456789101112^SMITH/JOHN ^12061010000000000”
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim CardNum As String
Dim LastName As String
Dim FirstName As String
Dim ExpMonth As Integer
Dim ExpYear As Integer
a = InStr(CardField1, “^”)
CardNum = Mid(CardField1, 3, a – 3)
b = InStr(CardField1, “/”)
LastName = Mid(CardField1, a + 1, b – a – 1)
c = InStr(b + 1, CardField1, “^”)
FirstName = Trim(Mid(CardField1, b + 1, c – b – 1))
ExpMonth = Mid(CardField1, c + 1, 2)
ExpYear = Mid(CardField1, c + 3, 2)

I hope you can use this as a starting point.

Reply | Quote

Thanks Hans. I’m going to start working with this and see how far I get… Appreciate your guidance. I’ll probably be back!

Here’s a sample database that uses expressions only in a query, no VBA.

Reply | Quote

Hans, this is great and simple enough for the small application it will be used in.

Thank you.

Here’s a sample database that uses expressions only in a query, no VBA.

Reply | Quote