Question seperating data continued

[mitch_johnson]

hello last week i asked for some code to seperate datafrom one field into three new fields i had some code posted which works great it was:

Me.Cardnum = Left(Me.Text12, 16)
Me.Expirydate = Mid(Me.Text12, 17, 4)
Me.Startdate = Right(Me.Text12, 4)

please could you add to this and tell me how i would do the following i have a magnetic card reader when i scan a credit card i get the following information go into a field:
;6767094116363345=10072010000003757170?
what i want to do is on a click of a button delete the ; from the begining put the first 16 digits (6767094116363345) into a one fild delete the = sign and copy the last 20 digits into another field and finaly delete the ? sign does anybody know how i can do this thanks

 

[Mike375]

Copy and paste the following into a module and see the query expressions below

Public Function NOnly(strOriginalString As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String
lngLength = Len(strOriginalString)
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strOriginalString, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If intAscii >= 48 And intAscii <= 57 Then
strFixed = strFixed & strTheCharacter
End If
Next lngCtr
NOnly = strFixed
End Function

Then create two new fields in a query

Exp: Right(NOnly([YourFieldName]),20)

Exp1: Left(NOnly([YourFieldName]),16)

The function NOnly extracts only numbers from a string as so drops = ; ? and any spaces

 

[mitch_johnson]

thank you i am quite new to access so could you please give me a more step by step answer and just to clarafy i want this to work in a form thanks

 

[DCrake]

First remove the offending characters

NewString = Replace(oldstring,";","")
NewString = Replace(NewString,"=","")
NewString = Replace(NewString,"?","")

Finally split the new string into 2 elements for the desired fields

Field1 = Left(NewString,16)
Field2 = Right(NewString,20)


David

 

[mitch_johnson]

sorry here is the code i already have:
Me.Cardnum = Left(Me.Text12, 16)
Me.Expirydate = Mid(Me.Text12, 17, 4)
Me.Startdate = Right(Me.Text12, 4)
this is the data i want to spiit it is in a text box named Text12:
;6767094116363345=10072010000003757170?
i want that split in a form in access please could you do me an example and step by step for that thanks a lot

 

[Mike375]

thank you i am quite new to access so could you please give me a more step by step answer and just to clarafy i want this to work in a form thanks

Either what David or I gave you can be in a form. You can create new fields in the query that supports the form you use.

Alternatively they can be used as the data source for unbound text boxes. In that case you put an = sign before the expressions as opposed to a new field name.

 

[mitch_johnson]

ok thanks would you mind doing an example with the code i posted before so i know what i should be putting exacly thanks

 

[Mike375]

For this ;6767094116363345=10072010000003757170?
do like in the early part of your post.

If put Me!xyz = left(NOnly([abc]), 16) behind a button for OnClick and click then 6767094116363345 will go into field [xyz]

However, you need to paste the code I gave you into a module.

What goes into a module is a function you make yourself. Things like Left(), Right() etc are functions that come stadard with Access. The function I have made is used just like you would use Left() except it extracts all numbers and only numbers from a string.

If the Left was not included in left(NOnly([abc]), 16) and it was just NOnly(abc]) then

;6767094116363345=10072010000003757170? would turn into

676709411636334510072010000003757170 so the Left and Right is then being applied to the string of numbers. The disdvantage of my NOnly is that it will drop all characters except numbers and that includes spaces. But it can be modified.

This part If intAscii >= 48 And intAscii <= 57 Then
is determining what is extracted.

Alternatively you can use Replace() as David suggested.

Make a copy of your DB and play around with the stuff yourself.

 

[Mike375]

PS,

If you want to play around you could probably do what you want with this

;6767094116363345=10072010000003757170?

using a mixture of Left(), Right(), Mid() and InStr() and perhaps Len()