spit one field into two, numbers in one, and letters in other.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a parts table that the part number and description are in the same
field. example: 253-222 widgets. I want to figure out a way to put the
numbers in one field and the letters in another field. I have several
thousand parts and would like to know if there is a query, or a code or SQL
that I can use to do this task? I want to end up with the part # field and
the description field instead of the "item" field that I currently have.
Thanks in advance.
 
Are there rules in that field, like
There is always numbers and text?
Or not, there could be only numbers or text.

Is there is space between them?

You can use for the number
Left([FieldName],Instr([FieldName], " ")-1)

For text
Mid([FieldName],Instr([FieldName], " ")+1)
 
in the letters only there are only letters. The numbers do contain letters,
but are always stated first;
NZ2345-R main bearing case.
I beleive I could use the Left([FieldName],Instr([FieldName], " ")-1).
I am pretty new at programming. I assume this formula would be placed in the
criteria area of the query.
After the results, I can make two new fields and delete the old one.
I will run a test on it later this week. I am quite busy doing at the monent.
Thanks for the responce

Ofer Cohen said:
Are there rules in that field, like
There is always numbers and text?
Or not, there could be only numbers or text.

Is there is space between them?

You can use for the number
Left([FieldName],Instr([FieldName], " ")-1)

For text
Mid([FieldName],Instr([FieldName], " ")+1)

--
Good Luck
BS"D


Access G-man said:
I have a parts table that the part number and description are in the same
field. example: 253-222 widgets. I want to figure out a way to put the
numbers in one field and the letters in another field. I have several
thousand parts and would like to know if there is a query, or a code or SQL
that I can use to do this task? I want to end up with the part # field and
the description field instead of the "item" field that I currently have.
Thanks in advance.
Click to expand...
Click to expand...
 
The rules are numbers are first. The numbers may contain letters IE;
32ZRP503. There is a space between the first sequence of numbers/letters and
the rest of the letters. I do not believe there are any numbers after the
first set.
Because I am new at programming, the formaula that you set me up with would
go into the criteria portion of the field in a query?

Ofer Cohen said:
Are there rules in that field, like
There is always numbers and text?
Or not, there could be only numbers or text.

Is there is space between them?

You can use for the number
Left([FieldName],Instr([FieldName], " ")-1)

For text
Mid([FieldName],Instr([FieldName], " ")+1)

--
Good Luck
BS"D


Access G-man said:
I have a parts table that the part number and description are in the same
field. example: 253-222 widgets. I want to figure out a way to put the
numbers in one field and the letters in another field. I have several
thousand parts and would like to know if there is a query, or a code or SQL
that I can use to do this task? I want to end up with the part # field and
the description field instead of the "item" field that I currently have.
Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top