String Manipulation woes

B

bobdydd

Hi Everybody

I am trying to use string functions to extract data from a field
called Me.txtDataRaw
and put part of it into another field called Me.txtCurrent


Me.txtDataRaw gets data as shown in the 3 examples below

"HSBA.L",
943.795,"10/10/2007","5:29am",-4.2049,949.00,949.00,942.00,8569749
"RBS.L",
551.50,"10/10/2007","5:30am",-6.50,562.00,562.00,549.00,7200732
"DGE.L",
1080.00,"10/10/2007","5:31am",-16.00,1094.00,1100.00,1080.00,2527346

What I would like to retrieve is the part between the 1st and 2nd
commas eg:
943.795
551.50
1080.00
And put it into Me.txtCurrent

It doesn't have to loop through all records just one at a time

Can anyone help me out?
Thanks in advance
 
S

Stuart McCall

bobdydd said:
Hi Everybody

I am trying to use string functions to extract data from a field
called Me.txtDataRaw
and put part of it into another field called Me.txtCurrent


Me.txtDataRaw gets data as shown in the 3 examples below

"HSBA.L",
943.795,"10/10/2007","5:29am",-4.2049,949.00,949.00,942.00,8569749
"RBS.L",
551.50,"10/10/2007","5:30am",-6.50,562.00,562.00,549.00,7200732
"DGE.L",
1080.00,"10/10/2007","5:31am",-16.00,1094.00,1100.00,1080.00,2527346

What I would like to retrieve is the part between the 1st and 2nd
commas eg:
943.795
551.50
1080.00
And put it into Me.txtCurrent

It doesn't have to loop through all records just one at a time

Can anyone help me out?
Thanks in advance
Click to expand...

Dim a As Variant

a = split(Me.txtDataRaw, ",")
Me.txtCurrent = a(1)

Take a look at help for the Split function.
 
B

bobdydd

a = split(Me.txtDataRaw, ",")
Me.txtCurrent = a(1)

Take a look at help for the Split function.- Hide quoted text -
Click to expand...

Thank you Stuart.....It did exactly what I wanted it to do.
I have been messing about with left and Instr functions to no avail
but your code did it straight off.

I checked out what the VBA help file said about split functions
but there was not much enlightenment to be had there (Access (2007)

When I get some time I will read up on the split function

Once again thanks millions
 
S

Stuart McCall

I checked out what the VBA help file said about split functions
but there was not much enlightenment to be had there (Access (2007)
Click to expand...

It isn't too hard to understand. The Split function takes as its 1st
parameter a delimited string. This can be delimited by just about anything -
in your case it was commas, hence I supplied "," as the 2nd parameter. The
function returns a variant array with one element per item-between-commas,
The elements are numbered starting from zero, which is why I specified a(1).

There's also a Join function which does exactly the opposite of Split (takes
a variant array and joins the elements into a delimited string)

Hope that helps a bit.
 
Top