splitting/dividing/seperating a field into 3 fields

[Guest]

I have the following data, it can appear in two forms

1 / 1450040 / ROBINSON
1 / 29220

I need to split into three separate colums, I have tried to us in Instr and
InstrRev, but can't get them to work just right any ideas?

 

[Jeff Boyce]

Jess

What is the source of the data?

The second form does not appear to include a field-delimiter (i.e., it looks
like it only has two fields).

How would you explain to a person how to tell the difference between the two
forms? Now get even more specific -- Access is not even as smart as your
helper.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Albert D. Kallal]

For your query, you can use:


myv1:trim(split([dataField],"/")(0))

myv2:trim(split([dataField],"/")(1))

myv3:trim(split([dataField],"/")(2))


Try the above expressions in a query. if it works, then you can change the
query to a update query, and send those values to 3 new fields...

 

[Guest]

I try the fuction as
myv1:trim(split([check/dist],"/")(0))
I get an invalid.dot or operator error

I put a comma prior to the (0) and get the following

myv1:trim(split([check/dist],"/"),(0)) but I get an
"wrong number of agruments" error,

What I am doing is taking data from one of my clients software via Excel and
importing their check register into Peachtree

I sincerely appreciate the help and the opportuntiy to learn from you all.

 

[Guest]

Jeff,
I am working with an excel file provided by my client, the data is
1 the bank account
2 the GL Account number
3 the Related Job (if there is one).

I then import the date into Peach tree

 

[Jeff Boyce]

Jess

This newsgroup is dedicated to using Microsoft Access, the relational
database. If you are importing into PeachTree, you might want to post in a
PeachTree newsgroup.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
I know the form the data has to be in to import into peachtree, the problem
is getting the data contained in one field coming from some constructin
software split into three seperate fields to import into Peachtree, and I
want to use access to split the field and update some other data prior to the
import.

 

[Albert D. Kallal]

Hum, you are correct.....

That expression I supplied don't work. (my mistake).

I assumed that query builder would accept that function...and it does not!!

Ok, lets create a public function, and use that!!!

create a standard code module (allow the default name of module1 for the
name).

Then, paste in the following code:

Public Function Pluck(vData As Variant, intToken) As Variant

If IsNull(vData) Then Exit Function

Pluck = Trim(Split(vData, "/")(intToken))

End Function


Now, for the query expressions use:

i1: Pluck([check/dist],0)
i2: Pluck([check/dist],1)
i3: Pluck([check/dist],2)

That will get you your 3 columns. You can export that query...or do whatever
you need...

 

[Guest]

albert,
This worked pretty well, however if column 3 is "null" I get an error asking
for debug, what can I do in order for column 3 to be blank if there is no JOB
ID which is the data for column 3

 

[Albert D. Kallal]

Ah...ok...

Well, lets just take the easy solution:

Public Function Pluck(vData As Variant, intToken) As Variant

If IsNull(vData) Then Exit Function

On Error Resume Next
Pluck = Trim(Split(vData, "/")(intToken))

End Function


Just put a on error resume next as above...and it will skip out with a
null....

 

[Guest]

albert,
That modual code give everything in column 3 null,empty
I have been trying to use
Expr1: LTrim(Mid(

,InStr(InStr([code],"/")+1,[code],"/")+1))
to give me my job code(column3), where my data in the field is one of the
following to forms

1 / 14201
1 / 1431110 / ROBINSON

that expr1: give
1 / 14201 and
ROBINSON

obviously I just need robinson or an empty field.

as ideas,

Sorry about not getting back to you, do US Tax returns for about 8 clients
all day yesterday.

Thanks your assistance...
Jess

 

[Albert D. Kallal]

albert,
That modual code give everything in column 3 null,empty

The code I posted was:

Public Function Pluck(vData As Variant, intToken) As Variant

If IsNull(vData) Then Exit Function

On Error Resume Next
Pluck = Trim(Split(vData, "/")(intToken))

End Function


in the debug window, I type in:

? pluck("1 / 14201",2)

nothing is CORRECTLY returned for the above ((reember, 0 = frst, 1 = 2nd,
and 2 = 3rd)

In the dbug window, I get:

? pluck(" 1 / 1431110 / ROBINSON",2)
ROBINSON

so, I do'nt know why my example does not work for you, but I just tested
it..and it works fine:

In the query builder use:


i1: Pluck([check/dist],0)
i2: Pluck([check/dist],1)
i3: Pluck([check/dist],2)

If the use of "0" for the first column is too large of a mind leap for you,
then change the function to:


Public Function Pluck(vData As Variant, intToken) As Variant

If IsNull(vData) Then Exit Function

On Error Resume Next
Pluck = Trim(Split(vData, "/")(intToken - 1))

End Function

then use

i1: Pluck([check/dist],1)
i2: Pluck([check/dist],2)
i3: Pluck([check/dist],3)

The orginal function is "zero" based..and thus 0 = 1st value, 1 = 2nd value,
and 2 = 3rd value

 

[Guest]

Albert I got it, It all works well.
Thanks for your assistance!!!

--
Just Jess

albert,
That modual code give everything in column 3 null,empty

The code I posted was:

Public Function Pluck(vData As Variant, intToken) As Variant

If IsNull(vData) Then Exit Function

On Error Resume Next
Pluck = Trim(Split(vData, "/")(intToken))

End Function


in the debug window, I type in:

? pluck("1 / 14201",2)

nothing is CORRECTLY returned for the above ((reember, 0 = frst, 1 = 2nd,
and 2 = 3rd)

In the dbug window, I get:

? pluck(" 1 / 1431110 / ROBINSON",2)
ROBINSON

so, I do'nt know why my example does not work for you, but I just tested
it..and it works fine:

In the query builder use:


i1: Pluck([check/dist],0)
i2: Pluck([check/dist],1)
i3: Pluck([check/dist],2)

If the use of "0" for the first column is too large of a mind leap for you,
then change the function to:


Public Function Pluck(vData As Variant, intToken) As Variant

If IsNull(vData) Then Exit Function

On Error Resume Next
Pluck = Trim(Split(vData, "/")(intToken - 1))

End Function

then use

i1: Pluck([check/dist],1)
i2: Pluck([check/dist],2)
i3: Pluck([check/dist],3)

The orginal function is "zero" based..and thus 0 = 1st value, 1 = 2nd value,
and 2 = 3rd value