Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-18-2009, 10:59 AM   #1
mitch_johnson
Registered User
 
Join Date: Mar 2009
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
mitch_johnson is on a distinguished road
Angry How Do I Seperate Data From One Field Into Multiple Fields

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

mitch_johnson is offline   Reply With Quote
Old 03-18-2009, 11:28 AM   #2
MSAccessRookie
AWF VIP
 
MSAccessRookie's Avatar
 
Join Date: May 2008
Location: Central NJ, USA
Posts: 3,428
Thanks: 13
Thanked 128 Times in 123 Posts
MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough MSAccessRookie is a jewel in the rough
Re: How Do I Seperate Data From One Field Into Multiple Fields

Quote:
Originally Posted by mitch_johnson View Post
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.
__________________
No one is expected to know everything and we can all learn from each other if we try.

This forum is about taking the next step (or perhaps the next two steps) toward becoming a better Access programmer, and the AWF Forum Members will help you on your journey.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MSAccessRookie is offline   Reply With Quote
Old 03-18-2009, 11:54 AM   #3
FoFa
Newly Registered User
 
FoFa's Avatar
 
Join Date: Jan 2003
Location: Texas, USA
Posts: 3,672
Thanks: 0
Thanked 4 Times in 4 Posts
FoFa is on a distinguished road
Re: How Do I Seperate Data From One Field Into Multiple Fields

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)

__________________
E Pluribus Unum - Out of Many, One
FoFa is offline   Reply With Quote
Old 03-18-2009, 01:49 PM   #4
mitch_johnson
Registered User
 
Join Date: Mar 2009
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
mitch_johnson is on a distinguished road
Re: How Do I Seperate Data From One Field Into Multiple Fields

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
mitch_johnson is offline   Reply With Quote
Old 03-18-2009, 02:01 PM   #5
mitch_johnson
Registered User
 
Join Date: Mar 2009
Posts: 40
Thanks: 0
Thanked 0 Times in 0 Posts
mitch_johnson is on a distinguished road
Re: How Do I Seperate Data From One Field Into Multiple Fields

That works Perfectly thanks a lot
mitch_johnson is offline   Reply With Quote
Old 03-18-2009, 02:18 PM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: How Do I Seperate Data From One Field Into Multiple Fields

Quote:
Originally Posted by mitch_johnson View Post
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/strive4peac...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/...5/Default.aspx

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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Tags
access , access 2007 , data , forms , seperate

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating Data In One Field Should Update Data In All Relevant Fields fkk Forms 5 02-18-2009 09:39 AM
Preventing duplicate data from multiple fields CTQE Tables 4 03-04-2008 02:44 PM
combining data from fields into a single field mgovatos Tables 4 06-06-2003 12:52 PM




All times are GMT -8. The time now is 12:25 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World