Parse

[jeffsal]

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

 

[AlexCuse]

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

 

[jeffsal]

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?

 

[AlexCuse]

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

 

[jeffsal]

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.

 

[PHV]

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

 

[AlexCuse]

Couldn't have said it better myself

Ignorance of certain subjects is a great part of wisdom