Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parse

Status
Not open for further replies.

jeffsal

Technical User
Oct 29, 2005
24
US
I have a field in a table that has constant numbers and variable numbers for instance:

2110 36.54 2120 300.12 2130 24.64

The numbers with decimals change but the other ones are constant in each record. There are always 12 spaces between the constant numbers and the variable numbers but varies between the end of the varible and the next constant number. I need to parse out the variable numbers into seperate fields in a query.
Field1..........Field2..........Field3
36.54...........300.12..........24.64

Hope it's clear enough. Thanks
 
Looks like its' tab delimited. If its' not, and you can't key off of a certain number of spaces, you will probably need to use a function to loop through your field and pluck out the numbers you need. Is it always the same 'number' of numbers?

Also, if at all possible you should try to redesign your table so that it does not contain this column. You should only have one value stored in each column.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Always the same 'number' of numbers. Constant numbers are always the same. Always 12 spaces between the constant numbers and the numbers I want. So for field1, I would like to return the number 12 spaces after "2110". Could I use something like string.split in some way?
 
Is field2 the same number of spaces after field1?

I think String.Split is VB.net, but yes, you can use Access VBA's string manipulation functions to do this. When knowing how everything is spaced, it will be easier to offer a solution.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
2110<12 spaces>"Number I want"<variable number of spaces>2120<12 spaces>"Number I want"<Variable number of spaces>2130<12 spaces>"Number I want". Could I use the constant numbers as delimiters? Thanks for helping.
 
Have a look at the Val, Mid and InStr functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Couldn't have said it better myself :)

Ignorance of certain subjects is a great part of wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top