Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

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
0
rustyroo
Asked:
rustyroo
1 Solution
 
Kent DyerIT Security Analyst SeniorCommented:
Look into the use of SPLIT on the ":" and that should do the trick for you.

HTH,

Kent
0
 
duttcomCommented:
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now