I need to cut, strip out and rearrange a text string into 2 fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

efandango - 26 Oct 2007 22:38 GMT

How can I cut text fields in a query like the example below,

-0.132861,51.591273,0

where the first part up to the first comma becomes one field, and the second
part, excluding the last comma and trailing zero becomes two (new) seperate
fields

Lon: -0.132861
Lat: 51.591273

a sample list of typcial data that I have to change.

coordinates
-0.132861,51.591273,0
-0.116207,51.59672,0
-0.101489,51.599326,0
-0.112013,51.5948,0
-0.09749200000000001,51.593865,0
-0.09479799999999999,51.593184,0
-0.111989,51.603028,0
-0.120467,51.598811,0

Reply to this Message

Jeff Boyce - 26 Oct 2007 22:48 GMT

Take a look at Access HELP on the Left(), Mid(), and InStr() functions.

Generically, you'll use a query and these functions to tell Access to create
two new (output) fields, taking everything to the left of the first comma as
one and everything following the first comma as the other.

Regards

Jeff Boyce
Microsoft Office/Access MVP

> How can I cut text fields in a query like the example below,
>
[quoted text clipped - 20 lines]
> -0.111989,51.603028,0
> -0.120467,51.598811,0

Reply to this Message

efandango - 26 Oct 2007 23:03 GMT

Thanks Jeff, I'll take a look

> Take a look at Access HELP on the Left(), Mid(), and InStr() functions.
>
[quoted text clipped - 31 lines]
> > -0.111989,51.603028,0
> > -0.120467,51.598811,0

Reply to this Message