how to seperate digit from string.

G

Guoqi Zheng

Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits, one got
the letters. For example, 20St, I need to seperate them into one column with
20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com
 
L

Lynn Trapp

Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS NumericPart,
Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;
 
P

Peter Frey

Hi Lynn,

but if one of the values were 333d3000, you'd get an error. Do you know a
solution for this problem, too?

Thanks a lot, Peter

Lynn said:
Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS
NumericPart, Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;


Guoqi Zheng said:
Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits,
one got the letters. For example, 20St, I need to seperate them into
one column with 20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com
Click to expand...
Click to expand...

--
Mit freundlichen Grüßen

Peter Frey, DATEX GmbH
Am Sandfeld 17a - 76149 Karlsruhe
Tel. 0721-7838060, Fax 0721-7838033
[email protected] http://www.datex.de
 
L

Lynn Trapp

You'll have to check for the presence of the letter "D" in the string,
because Val() is evaluating that as a Decimal value. This should get you on
the right track:


IIf(InStr([YourField],"D"),Left([YourField],InStr([YourField],"D")-1),Val([Y
ourField]))

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Peter Frey said:
Hi Lynn,

but if one of the values were 333d3000, you'd get an error. Do you know a
solution for this problem, too?

Thanks a lot, Peter

Lynn said:
Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS
NumericPart, Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;


Guoqi Zheng said:
Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits,
one got the letters. For example, 20St, I need to seperate them into
one column with 20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com
Click to expand...
Click to expand...

--
Mit freundlichen Grüßen

Peter Frey, DATEX GmbH
Am Sandfeld 17a - 76149 Karlsruhe
Tel. 0721-7838060, Fax 0721-7838033
[email protected] http://www.datex.de
Click to expand...
 
P

Peter Frey

Hi Lynn,

thank you, I'll give it a try.

Lynn said:
You'll have to check for the presence of the letter "D" in the string,
because Val() is evaluating that as a Decimal value. This should get
you on the right track:


IIf(InStr([YourField],"D"),Left([YourField],InStr([YourField],"D")-1),Val([Y
ourField]))


Peter Frey said:
Hi Lynn,

but if one of the values were 333d3000, you'd get an error. Do you
know a solution for this problem, too?

Thanks a lot, Peter

Lynn said:
Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS
NumericPart, Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;


Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits,
one got the letters. For example, 20St, I need to seperate them
into one column with 20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com
Click to expand...

--
Mit freundlichen Grüßen

Peter Frey, DATEX GmbH
Am Sandfeld 17a - 76149 Karlsruhe
Tel. 0721-7838060, Fax 0721-7838033
[email protected] http://www.datex.de
Click to expand...
Click to expand...
 
Top