By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,489 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,489 IT Pros & Developers. It's quick & easy.

problem with number and char formatting..

P: n/a
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..

Aug 29 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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..
Aug 29 '06 #2

P: n/a
(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..
Aug 29 '06 #3

P: n/a
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
Aug 29 '06 #4

P: n/a
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..
Aug 30 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.