parse data from a string containg spaces 2

[kiwieur]

I wonder if someone can help please, I have a field in a db table that contains a string made up of 5 numbers seperated by spaces a sample of which is shown below

30 363 253 363 250
30 258 98 258 95
30 333 293 333 290

if the spaces were always in the same place I would use Left, Mid etc.
What i would like to do is split these values into 5 seperate fields, could someone explain how I could do this please




Regards

Paul

 

[HarleyQuinn]

If you want to do this using VBA I'd have a look at the Split() function, does exactly what you're after.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[alvechurchdata]

HarleyQuinn's right, Split() will do the job. You might need to Trim() beforehand to get rid of any leading or trailing spaces. I don't know whether these will confuse the Split() process.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[kiwieur]

Hi guys never used split before what I was hoping to do was extract the values from a field in a query and put them into 5 seperate fields

so my original field is called panels and the values are

30 363 253 363 250

so then I would have additional five fields shown as so

panel1 = 30 panel2 = 363 panel3 = 253 panel4 = 363 panel5 = 250

is this possible ??


Regards

Paul

 

[HarleyQuinn]

Hi,

Pop the following into a module (please note, it is a very basic example and has no data integrity checks or error handling but it should give you the idea)

Public Function SplitField(varField As Variant, position As Integer) As String

SplitField = Split(varField, " ")(position)

End Function

Then you could use it in a query, for example like this:

SELECT panels, 
splitfield([panels],0) AS panel1, 
splitfield([panels],1) AS panel2, 
splitfield([panels],2) AS panel3, 
splitfield([panels],3) AS panel4, 
splitfield([panels],4) AS panel5
FROM tbl_Panels;

Bear in mind that in it's current state it is zero based so for the first value use 0 in the function etc. (as is demonstrated above).

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[dhookom]

To find out how to use the Split() function"
1) Open the debug window (press Ctrl+G)
2) Enter the following;

? Split("30 363 253 363 250")(0)

You should see

? Split("30 363 253 363 250")(0)
30

or

? Split("30 363 253 363 250")(4)
250

The following will produce an error

? Split("30 363 253 363 250")(5)

To put this in a query, the sql might look like

SELECT panels, 
Split([panels],0) AS panel1, 
Split([panels],1) AS panel2, 
Split([panels],2) AS panel3, 
Split([panels],3) AS panel4, 
Split([panels],4) AS panel5
FROM tbl_Panels;

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

oops correction :-(

SELECT panels, 
Split([panels])(0) AS panel1, 
Split([panels])(1) AS panel2, 
Split([panels])(2) AS panel3, 
Split([panels])(3) AS panel4, 
Split([panels])(4) AS panel5
FROM tbl_Panels;

Duane
Hook'D on Access
MS Access MVP

 

[HarleyQuinn]

Duane, what version of Access?

I've always used Split in a UDF (partly to allow proper error handling) as I couldn't use it directly in a query in 2002.

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[kiwieur]

Guys,

Thanks for the help on this I have tried both methods and they work great however I have now found a couple of issues that I do not know how to deal with.

In the table I was querying there were 2000 records and after using your code it highlighted that if the original field was empty then I get errors.

I could get around this i suppose by dynaically entering the following if the field is empty

0 0 0 0 0

also I found one record where the field only had 2 sets of figures in the string i.e.

150 150

and this also produced an error for the last three items in the array

Could you help me solve this please



Regards

Paul

 

[SkipVought]

You said, "...up to 5 numbers"

Are there fewer than 5?

If there are do they get assigned to Pnelx in sequence?

Are these Panel1 - Panel5 fields in a table?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kiwieur]

Hi Skip,

these values are all in a string in one field of a database table

why they are concatenated together in the first palce I do not know as this is data from one of our legacy systems. so I would like to extract these values and put them in seperate fields for use in a report I am doing.

I only realised after the guys had sent me the code that there were a couple of records where there were only 2 values in the field and also some of the records do not contain any values at all.



Regards

Paul

 

[dhookom]

Andy,
I stand corrected regarding using Split() bare-naked in a query. I tested mostly in the debug window and didn't realize the significant difference in the query. I suppose this is somewhat like the evaluation of true and false sections of IIf() which are different in a query and code.

To handle fewer than 5 panel values, you could use

SELECT panels, 
splitfield([panels] & " 0 0 0 0 0",0) AS panel1, 
splitfield([panels] & " 0 0 0 0 0",1) AS panel2, 
splitfield([panels] & " 0 0 0 0 0",2) AS panel3, 
splitfield([panels] & " 0 0 0 0 0",3) AS panel4, 
splitfield([panels] & " 0 0 0 0 0",4) AS panel5
FROM tbl_Panels;

I think a properly normalized table structure might have up to five (or more) related records in a panels table rather than 5 panel fields in a single table.

Duane
Hook'D on Access
MS Access MVP

 

[kiwieur]

Hi Duane,

worked like a dream thank you so much

Regards

Paul