split data problems

[Access G-man]

I read a lot of posts on how to split data in a field. The text field I have
is part numbers and descriptions of parts together. I want to seperate them
to part # and Descrioption. I ran this update query Left([part_#]), " ",
0)-1). Which was successful to an extent. I changed the part number and
description field to just part number field, which is what I wanted to do. I
lost all the description that goes with the Part numbers. I ran a make table
query to get two fields. part # and description. In the part # field are just
part numbers, in the description I put in my back up part numbers with
description. I now need to delete just the 1st text before the space. Would I
change the code to Right([part_#]), " ", 0)-1) in an update query? Or is
there an easy way to run a split code? I am not very experienced in code.

 

[Michel Walsh]

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP

 

[Access G-man]

Thank you for the fast reply, I am assuming I would put that in the update
query update to line

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP

I read a lot of posts on how to split data in a field. The text field I
have
is part numbers and descriptions of parts together. I want to seperate
them
to part # and Descrioption. I ran this update query Left([part_#]), " ",
0)-1). Which was successful to an extent. I changed the part number and
description field to just part number field, which is what I wanted to do.
I
lost all the description that goes with the Part numbers. I ran a make
table
query to get two fields. part # and description. In the part # field are
just
part numbers, in the description I put in my back up part numbers with
description. I now need to delete just the 1st text before the space.
Would I
change the code to Right([part_#]), " ", 0)-1) in an update query? Or is
there an easy way to run a split code? I am not very experienced in code.

 

[Access G-man]

I ran the code in the update query, 1st hang up to many closing parenthesis,
the second couldn't run the qeury due to "type conversion failure". I clicked
help to no avail.

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP

I read a lot of posts on how to split data in a field. The text field I
have
is part numbers and descriptions of parts together. I want to seperate
them
to part # and Descrioption. I ran this update query Left([part_#]), " ",
0)-1). Which was successful to an extent. I changed the part number and
description field to just part number field, which is what I wanted to do.
I
lost all the description that goes with the Part numbers. I ran a make
table
query to get two fields. part # and description. In the part # field are
just
part numbers, in the description I put in my back up part numbers with
description. I now need to delete just the 1st text before the space.
Would I
change the code to Right([part_#]), " ", 0)-1) in an update query? Or is
there an easy way to run a split code? I am not very experienced in code.

 

[Michel Walsh]

There is no real need to use an update query, a simple SELECT query can do
the job. Sure, if you prefer to make a better design, splitting the
information in two different field, that would be better and to do that, you
will need an update query, or a make table query.

It is hard to see where you have unmatched parentheses, in your real query.
You can try a simple SELECT:

SELECT InStr( yourString & " " , " " ) AS firstSpaceAt,
LEFT( yourString & " ", firstSpaceAt - 1 AS firstPart,
MID( yourString, 1+firstSpaceAt ) AS secondPart
FROM yourTableNameHere




Vanderghast, Access MVP

I ran the code in the update query, 1st hang up to many closing
parenthesis,
the second couldn't run the qeury due to "type conversion failure". I
clicked
help to no avail.

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP

I read a lot of posts on how to split data in a field. The text field I
have
is part numbers and descriptions of parts together. I want to seperate
them
to part # and Descrioption. I ran this update query Left([part_#]), "
",
0)-1). Which was successful to an extent. I changed the part number and
description field to just part number field, which is what I wanted to
do.
I
lost all the description that goes with the Part numbers. I ran a make
table
query to get two fields. part # and description. In the part # field
are
just
part numbers, in the description I put in my back up part numbers with
description. I now need to delete just the 1st text before the space.
Would I
change the code to Right([part_#]), " ", 0)-1) in an update query? Or
is
there an easy way to run a split code? I am not very experienced in
code.

 

[Access G-man]

Michaael,
Yes, I thought the paranthesis were proper, I will try the new code in
select query. I have already done a make table, with the first part of the
part number in part_# and now for the description field, I have to get rid of
all before the first space. And yes, I am trying to make a better design and
learning some manipulation tatics along the way!
I will try this later, today or tomorrow AM.

There is no real need to use an update query, a simple SELECT query can do
the job. Sure, if you prefer to make a better design, splitting the
information in two different field, that would be better and to do that, you
will need an update query, or a make table query.

It is hard to see where you have unmatched parentheses, in your real query.
You can try a simple SELECT:

SELECT InStr( yourString & " " , " " ) AS firstSpaceAt,
LEFT( yourString & " ", firstSpaceAt - 1 AS firstPart,
MID( yourString, 1+firstSpaceAt ) AS secondPart
FROM yourTableNameHere




Vanderghast, Access MVP

I ran the code in the update query, 1st hang up to many closing
parenthesis,
the second couldn't run the qeury due to "type conversion failure". I
clicked
help to no avail.

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP



I read a lot of posts on how to split data in a field. The text field I
have
is part numbers and descriptions of parts together. I want to seperate
them
to part # and Descrioption. I ran this update query Left([part_#]), "
",
0)-1). Which was successful to an extent. I changed the part number and
description field to just part number field, which is what I wanted to
do.
I
lost all the description that goes with the Part numbers. I ran a make
table
query to get two fields. part # and description. In the part # field
are
just
part numbers, in the description I put in my back up part numbers with
description. I now need to delete just the 1st text before the space.
Would I
change the code to Right([part_#]), " ", 0)-1) in an update query? Or
is
there an easy way to run a split code? I am not very experienced in
code.

 

[Access G-man]

SELECT InStr(description & " ", " ") AS firstSpaceAT, Left(description & " ",
firstSpaceAt -1) AS firstPart Mid(description, 1+ firstSpaceAt) As Second
Part
FROM tblParts;
I receive a compile error: InStr(description & " ", " ") . I am kind of
stumped. description is the field name which I assumed was the string.
Any Suggestions?

Michaael,
Yes, I thought the paranthesis were proper, I will try the new code in
select query. I have already done a make table, with the first part of the
part number in part_# and now for the description field, I have to get rid of
all before the first space. And yes, I am trying to make a better design and
learning some manipulation tatics along the way!
I will try this later, today or tomorrow AM.

There is no real need to use an update query, a simple SELECT query can do
the job. Sure, if you prefer to make a better design, splitting the
information in two different field, that would be better and to do that, you
will need an update query, or a make table query.

It is hard to see where you have unmatched parentheses, in your real query.
You can try a simple SELECT:

SELECT InStr( yourString & " " , " " ) AS firstSpaceAt,
LEFT( yourString & " ", firstSpaceAt - 1 AS firstPart,
MID( yourString, 1+firstSpaceAt ) AS secondPart
FROM yourTableNameHere




Vanderghast, Access MVP

I ran the code in the update query, 1st hang up to many closing
parenthesis,
the second couldn't run the qeury due to "type conversion failure". I
clicked
help to no avail.

:

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP



I read a lot of posts on how to split data in a field. The text field I
have
is part numbers and descriptions of parts together. I want to seperate
them
to part # and Descrioption. I ran this update query Left([part_#]), "
",
0)-1). Which was successful to an extent. I changed the part number and
description field to just part number field, which is what I wanted to
do.
I
lost all the description that goes with the Part numbers. I ran a make
table
query to get two fields. part # and description. In the part # field
are
just
part numbers, in the description I put in my back up part numbers with
description. I now need to delete just the 1st text before the space.
Would I
change the code to Right([part_#]), " ", 0)-1) in an update query? Or
is
there an easy way to run a split code? I am not very experienced in
code.

 

[Michel Walsh]

You may have a reference problem. In VBE (the development window), from the
menu, under Tools | References... is there a checked reference starting
its descriptive message with the word MISSING ? If so, this is the source
of your problem. You have to fix it (either uncheck that reference, either
find its path on the PC having the error).


I assume you use a coma as argument delimiter. If you use something else,
such as a semi colon, try it. In fact, you can try, in the immediate debug
window, rather than executing the query, by typing in it:

? InStr( "hello world" & " " , " " )


which should return 6.



Vanderghast, Access MVP

SELECT InStr(description & " ", " ") AS firstSpaceAT, Left(description & "
",
firstSpaceAt -1) AS firstPart Mid(description, 1+ firstSpaceAt) As Second
Part
FROM tblParts;
I receive a compile error: InStr(description & " ", " ") . I am kind of
stumped. description is the field name which I assumed was the string.
Any Suggestions?

Michaael,
Yes, I thought the paranthesis were proper, I will try the new code in
select query. I have already done a make table, with the first part of
the
part number in part_# and now for the description field, I have to get
rid of
all before the first space. And yes, I am trying to make a better design
and
learning some manipulation tatics along the way!
I will try this later, today or tomorrow AM.

There is no real need to use an update query, a simple SELECT query can
do
the job. Sure, if you prefer to make a better design, splitting the
information in two different field, that would be better and to do
that, you
will need an update query, or a make table query.

It is hard to see where you have unmatched parentheses, in your real
query.
You can try a simple SELECT:

SELECT InStr( yourString & " " , " " ) AS firstSpaceAt,
LEFT( yourString & " ", firstSpaceAt - 1 AS firstPart,
MID( yourString, 1+firstSpaceAt ) AS secondPart
FROM yourTableNameHere




Vanderghast, Access MVP



I ran the code in the update query, 1st hang up to many closing
parenthesis,
the second couldn't run the qeury due to "type conversion failure". I
clicked
help to no avail.

:

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP



message
I read a lot of posts on how to split data in a field. The text
field I
have
is part numbers and descriptions of parts together. I want to
seperate
them
to part # and Descrioption. I ran this update query
Left([part_#]), "
",
0)-1). Which was successful to an extent. I changed the part
number and
description field to just part number field, which is what I
wanted to
do.
I
lost all the description that goes with the Part numbers. I ran a
make
table
query to get two fields. part # and description. In the part #
field
are
just
part numbers, in the description I put in my back up part numbers
with
description. I now need to delete just the 1st text before the
space.
Would I
change the code to Right([part_#]), " ", 0)-1) in an update query?
Or
is
there an easy way to run a split code? I am not very experienced
in
code.

 

[Access G-man]

Michael,
Yes, I did have a problem in the VBA. I had a code in there that was bad.
Instead of repairing it, or deleting it I left it. Anytime that I would run a
code after that wether it were correct or not I received the compile error
code due to the mistake in VBA. I deleted the code, ran the Select query code
you gave me. It split the sinigle field of data into three fields. The three
I wanted@!
Thank you so much for your time and the sharing of your knowledge.
--
Grant
Stumblin'' and Bumblin'', but gettin it done!!

You may have a reference problem. In VBE (the development window), from the
menu, under Tools | References... is there a checked reference starting
its descriptive message with the word MISSING ? If so, this is the source
of your problem. You have to fix it (either uncheck that reference, either
find its path on the PC having the error).


I assume you use a coma as argument delimiter. If you use something else,
such as a semi colon, try it. In fact, you can try, in the immediate debug
window, rather than executing the query, by typing in it:

? InStr( "hello world" & " " , " " )


which should return 6.



Vanderghast, Access MVP

SELECT InStr(description & " ", " ") AS firstSpaceAT, Left(description & "
",
firstSpaceAt -1) AS firstPart Mid(description, 1+ firstSpaceAt) As Second
Part
FROM tblParts;
I receive a compile error: InStr(description & " ", " ") . I am kind of
stumped. description is the field name which I assumed was the string.
Any Suggestions?

Michaael,
Yes, I thought the paranthesis were proper, I will try the new code in
select query. I have already done a make table, with the first part of
the
part number in part_# and now for the description field, I have to get
rid of
all before the first space. And yes, I am trying to make a better design
and
learning some manipulation tatics along the way!
I will try this later, today or tomorrow AM.

:

There is no real need to use an update query, a simple SELECT query can
do
the job. Sure, if you prefer to make a better design, splitting the
information in two different field, that would be better and to do
that, you
will need an update query, or a make table query.

It is hard to see where you have unmatched parentheses, in your real
query.
You can try a simple SELECT:

SELECT InStr( yourString & " " , " " ) AS firstSpaceAt,
LEFT( yourString & " ", firstSpaceAt - 1 AS firstPart,
MID( yourString, 1+firstSpaceAt ) AS secondPart
FROM yourTableNameHere




Vanderghast, Access MVP



I ran the code in the update query, 1st hang up to many closing
parenthesis,
the second couldn't run the qeury due to "type conversion failure". I
clicked
help to no avail.

:

You can split a string with MID (see help file)


? MID( "abc-1234", 5)
1234



So:

MID( yourString, 1+InStr( yourString & " ", " " ) )


should retrieve every thing that comes after the first space.




Vanderghast, Access MVP



message
I read a lot of posts on how to split data in a field. The text
field I
have
is part numbers and descriptions of parts together. I want to
seperate
them
to part # and Descrioption. I ran this update query
Left([part_#]), "
",
0)-1). Which was successful to an extent. I changed the part
number and
description field to just part number field, which is what I
wanted to
do.
I
lost all the description that goes with the Part numbers. I ran a
make
table
query to get two fields. part # and description. In the part #
field
are
just
part numbers, in the description I put in my back up part numbers
with
description. I now need to delete just the 1st text before the
space.
Would I
change the code to Right([part_#]), " ", 0)-1) in an update query?
Or
is
there an easy way to run a split code? I am not very experienced
in
code.