Question How Do I Seperate Data From One Field Into Multiple Fields (1 Viewer)

mitch_johnson

Registered User.
Local time
Yesterday, 22:58
Joined
Mar 18, 2009
Messages
40
Hello i am quite new to access i have been using it for a couple of months now and am getting the hang of it i dont know any VB code but i know all about how to put code in and how to use the properties window (i am using access 2007) what i want to do is as follows:
1. i have a magnetic card reader (KEYBOARD emulation input device) when i scan a card it displays data all in one place so on a card is the card number, the expiry date and start date so i would scan in like follows: 456798987898009009080911 with red being the card number, green the start date and blue the expiry what i want to do is be able to click a button and seperate the three pieces of data into seperate fields EG: card number (RED) into a field names card number ETC.... this is all in a form in access incase you were wondering please help i have looked everywere on the internet with no luck. thanks
 

MSAccessRookie

AWF VIP
Local time
Today, 01:58
Joined
May 2, 2008
Messages
3,428
Hello i am quite new to access i have been using it for a couple of months now and am getting the hang of it i dont know any VB code but i know all about how to put code in and how to use the properties window (i am using access 2007) what i want to do is as follows:
1. i have a magnetic card reader (KEYBOARD emulation input device) when i scan a card it displays data all in one place so on a card is the card number, the expiry date and start date so i would scan in like follows: 456798987898009009080911 with red being the card number, green the start date and blue the expiry what i want to do is be able to click a button and seperate the three pieces of data into seperate fields EG: card number (RED) into a field names card number ETC.... this is all in a form in access incase you were wondering please help i have looked everywere on the internet with no luck. thanks

As long as the string will have a fixed length and structure, Access has three built in Functions (Left(), Right(), and Mid()), that can help you to do what you want to do. Look them up and see if you can get them to work. Post back if you have any questions.
 

FoFa

Registered User.
Local time
Today, 00:58
Joined
Jan 29, 2003
Messages
3,672
Where are you putting the values after you split it up?
If you populating 3 form fields, the easiest way would be VBA code. On the click event of your button, you would need something like this.
Assumptions form field AllNum = 456798987898009009080911
form field CardNum you want 4567989878980090 in
form field ExpireDate you want 0908
form field StartDate you want 0911

Me.CardNum = LEFT(Me.AllNum,16)
Me.ExpireDate = MID(me.AllNum,17,4)
Me.StartDate = RIGHT(Me.AllNum,4)
 

mitch_johnson

Registered User.
Local time
Yesterday, 22:58
Joined
Mar 18, 2009
Messages
40
Hi,

i get the basic idea of what you are saying would you mind just explaning in a little more deatils and add some step by step instructions thanks
 

boblarson

Smeghead
Local time
Yesterday, 22:58
Joined
Jan 12, 2001
Messages
32,059
That works Perfectly thanks a lot

Mitch -

It would appear that you are very new to Access, so I have a few resources that you might like to make a note of and read when you can:

Normalization:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

Free Access Tutorials:
http://www.functionx.com/access/index.htm

"Quick Tutorials" (mostly screenshots with step-by-steps):
http://www.btabdevelopment.com/main/QuickTutorials/tabid/55/Default.aspx

List of Access Reserved Words (words to not use as field and object names):
http://www.allenbrowne.com/AppIssueBadWord.html
 

Users who are viewing this thread

Top Bottom