problem with number and char formatting..

[leen]

I'm working in Access2000 and am having trouble finding a way to take
imported "OLEPath" field values in my database and filter out or
eliminate all the alpha text so we only get numeral values, allowing
for number formatting. only the first and the last 4 of every value is
text. Here's some examples taken from a table: "A1011.bmp" or
"A201.bmp". I need to get the values to read as "1011" and "201".
Is there a way to do this with SQL Select statement or VBA(if
impossible with SQL.)
plz...i really need a help..

[Phivos]

Greetings leen

Right(Left([1];Len([1])-4);Len(Left([1];Len([1])-4))-1) will give you
the part you want

It will still be a string though, not a number.

Val(Right(Left([1];Len([1])-4);Len(Left([1];Len([1])-4))-1)) takes
care of that also.

Phivos

leen wrote:

I'm working in Access2000 and am having trouble finding a way to take
imported "OLEPath" field values in my database and filter out or
eliminate all the alpha text so we only get numeral values, allowing
for number formatting. only the first and the last 4 of every value is
text. Here's some examples taken from a table: "A1011.bmp" or
"A201.bmp". I need to get the values to read as "1011" and "201".
Is there a way to do this with SQL Select statement or VBA(if
impossible with SQL.)
plz...i really need a help..

[Phivos]

(Where [1] in the example contains the string you want to process)

Phivos wrote:

Greetings leen

Right(Left([1];Len([1])-4);Len(Left([1];Len([1])-4))-1) will give you
the part you want

It will still be a string though, not a number.

Val(Right(Left([1];Len([1])-4);Len(Left([1];Len([1])-4))-1)) takes
care of that also.

Phivos

leen wrote:

I'm working in Access2000 and am having trouble finding a way to take
imported "OLEPath" field values in my database and filter out or
eliminate all the alpha text so we only get numeral values, allowing
for number formatting. only the first and the last 4 of every value is
text. Here's some examples taken from a table: "A1011.bmp" or
"A201.bmp". I need to get the values to read as "1011" and "201".
Is there a way to do this with SQL Select statement or VBA(if
impossible with SQL.)
plz...i really need a help..

[fredg]

On 28 Aug 2006 23:53:23 -0700, leen wrote:

I'm working in Access2000 and am having trouble finding a way to take
imported "OLEPath" field values in my database and filter out or
eliminate all the alpha text so we only get numeral values, allowing
for number formatting. only the first and the last 4 of every value is
text. Here's some examples taken from a table: "A1011.bmp" or
"A201.bmp". I need to get the values to read as "1011" and "201".
Is there a way to do this with SQL Select statement or VBA(if
impossible with SQL.)

plz...i really need a help..

If the first character is always a letter, then, in a query:

NumValue:Val(Mid(OLEPath],2))

will return the number value as a Number datatype.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[hbinc]

Hi Leen,

Put this code in a module, and use it as a normal function, e.g.

cur_value = Extr_int("A1011.bmp")
In this example cur_value = 1011.

Function Extr_int(cur_string As String) As Integer
Dim x As Integer
Dim cur_char As String
Dim new_string As String

For x = 1 To Len(cur_string)
cur_char = Mid(cur_string, x, 1)
Select Case cur_char
Case "0" To "9": new_string = new_string & cur_char
End Select
Next

Extr_int = CInt(new_string)
End Function
I assume it is easy to modify this function for Long integers, or add
further functionality in the case that you only want to have the first
serie of digits.

Success, HBInc.

leen wrote:

I'm working in Access2000 and am having trouble finding a way to take
imported "OLEPath" field values in my database and filter out or
eliminate all the alpha text so we only get numeral values, allowing
for number formatting. only the first and the last 4 of every value is
text. Here's some examples taken from a table: "A1011.bmp" or
"A201.bmp". I need to get the values to read as "1011" and "201".
Is there a way to do this with SQL Select statement or VBA(if
impossible with SQL.)
plz...i really need a help..