separate columns by numeric and letter values

[Mitchell_Collen via AccessMonster.com]

I have a column called strength, it contains values such as 5MG, 6.3ML, 100MU.
I want to create a new column containing on the unit values such as MG, ML,
MU... and another new column called volume with 5, 6.3, 100... Do you
know how to separate a column with letter values in one column and numeric
values in another. I only have Access 2002 and I don't know how to write VBA
yet. Please help me if you can. Thanks Misty

 

[Marshall Barton]

I have a column called strength, it contains values such as 5MG, 6.3ML, 100MU.
I want to create a new column containing on the unit values such as MG, ML,
MU... and another new column called volume with 5, 6.3, 100... Do you
know how to separate a column with letter values in one column and numeric
values in another. I only have Access 2002 and I don't know how to write VBA
yet.

Another good reason why each field should only have a single
meaning.

First, create the new fields in the table. Then, if the
units are always 2 characters, then you can use simple
expressions in an update query:

UPDATE thetable SET Unit=Right(strength,2),
Volume=Left(Strength, Len(strength)-2))

 

[Mitchell_Collen via AccessMonster.com]

Another good reason why each field should only have a single
meaning.

First, create the new fields in the table. Then, if the
units are always 2 characters, then you can use simple
expressions in an update query:

UPDATE thetable SET Unit=Right(strength,2),
Volume=Left(Strength, Len(strength)-2))

It varies more. The units can be up to 4 characters but are always letters
and the volume is always a number from 1 to 7 characters. Do you know of a
way to update with letters only in one column and numbers only in another
column?

 

[Klatuu]

Volume: Val(Strength)
Unit: Replace(Strength,Val(Strength),"")

Assuming the numeric part is always first, the Val function will return the
numeric part. The Replace will change the numeric part to a null string.

 

[KARL DEWEY]

Try this substituting your table name for Collen ----
UPDATE Collen SET Collen.Volume = Val([Strenght]), Collen.Units =
Right([Strenght],Len([Strenght])-Len(Val([Strenght])));

 

[Marshall Barton]

It varies more. The units can be up to 4 characters but are always letters
and the volume is always a number from 1 to 7 characters. Do you know of a
way to update with letters only in one column and numbers only in another
column?

I can't guarantee this in every conceivable situation (e.g.
missing or negative volumes), but I think it will work in
your case:

UPDATE thetable
SET Unit=Mid(strength,Len(Str(Val(strength)),
Volume=Left(strength, Len(Str(Val(strength)))-1)