need part of field in access only

Hey an easy one for anyone but me probably... need to separate this data from one field into 3

eg:

10:business name:$3.21
10         business name           $3.21

so i want one with 10, one with "business name" and one with $3.21

what would be the easiest way to do this, either using a modify query or vba macro?

appreciate the help.

Rusyt
rustyrooAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
duttcomConnect With a Mentor Commented:
When you say separate it into 3 - where do you want the field values to go?

Is this a once-off exercise, eg. Do you have a database already populated that you need to extract the 3 fields from once only, or will data be added in the 1-field format which will need to be split into 3 after entry?

Also, the data is already delineated by the ":" so it is already effectively split into 3 fields - you could use that knowledge to export the data into a text file, do a find and replace on the colons to convert them into commas, which would then give you the data in three fields as a CSV file which can be reimported.
0
 
Kent DyerIT Security Analyst SeniorCommented:
Look into the use of SPLIT on the ":" and that should do the trick for you.

HTH,

Kent
0
 
rustyrooAuthor Commented:
Thanks... it is one field of about 20 that i have to import.  The rest are easy.  My plan is to import the 20 fields to one table, then append this to another new table with the extra 3 columns which will be the main data table I use.  I will have to do this monthly, so plan to use this first table as a temp table on the way to appending.  
Hope that helps
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
duttcomCommented:
Where are you importing from? Is it a text file or some other source?

You may be able to split that field on import depending on the source.
0
 
rustyrooAuthor Commented:
it is a csv file
0
 
IrogSintaCommented:
As duttcom mentioned, you can parse this right when you import.  Just specify the colon ":" as your delimeter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.