By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

String Manipulation woes

P: n/a
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

Oct 10 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"bobdydd" <re**************@yahoo.co.ukwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...
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
Dim a As Variant

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

Take a look at help for the Split function.

Oct 10 '07 #2

P: n/a
a = split(Me.txtDataRaw, ",")
Me.txtCurrent = a(1)

Take a look at help for the Split function.- Hide quoted text -
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

Oct 10 '07 #3

P: n/a
"bobdydd" <re**************@yahoo.co.ukwrote in message
news:11*********************@o80g2000hse.googlegro ups.com...
I checked out what the VBA help file said about split functions
but there was not much enlightenment to be had there (Access (2007)
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.
Oct 10 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.