Solved

need part of field in access only

Posted on 2013-01-06
6
403 Views
Last Modified: 2013-01-06
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
Comment
Question by:rustyroo
6 Comments
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 38749574
Look into the use of SPLIT on the ":" and that should do the trick for you.

HTH,

Kent
0
 
LVL 12

Accepted Solution

by:
duttcom earned 500 total points
ID: 38749604
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
 

Author Comment

by:rustyroo
ID: 38749628
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
Technology Partners: 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!

 
LVL 12

Expert Comment

by:duttcom
ID: 38749641
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
 

Author Comment

by:rustyroo
ID: 38749722
it is a csv file
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38749780
As duttcom mentioned, you can parse this right when you import.  Just specify the colon ":" as your delimeter.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question