Pickup numbers from a field

C

CAM

Hello,

I have a field that has 12 digits - Example AS0090040101. What I want to do
is to pick up only the number 9004 and put it into another field in my
query. How do I do that? Any tips will be apperciated. Thank you.


Cheers
 
S

Stefan Hoffmann

hi,
I have a field that has 12 digits - Example AS0090040101. What I want
to do is to pick up only the number 9004 and put it into another field
in my query. How do I do that? Any tips will be apperciated. Thank you.
Click to expand...
When your field is not null you can use this in a query:

MyNumber: Mid([fieldName], 5, 4)



mfG
--> stefan <--
 
C

CAM

Thanks Stefan I appreciate the help.
Cheers

Stefan Hoffmann said:
hi,
I have a field that has 12 digits - Example AS0090040101. What I want to
do is to pick up only the number 9004 and put it into another field in my
query. How do I do that? Any tips will be apperciated. Thank you.
Click to expand...
When your field is not null you can use this in a query:

MyNumber: Mid([fieldName], 5, 4)



mfG
--> stefan <--
Click to expand...
 
R

raskew via AccessMonster.com

Hi -

You might try copying/pasting this to a standard module:

Function SaveNumer2(ByVal pStr As String) As Long
'*******************************************
'Purpose: Removes alpha characters from
' a string
'Coded by: raskew
'Calls: Function IsNumeric()
'Inputs: ? SaveNumer2("ABC234BB")
'Output: 234
'*******************************************

Dim strHold As String
Dim intLen As Integer
Dim n As Integer

strHold = Trim(pStr)
intLen = Len(strHold)
n = 1
Do
If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1))
Then
strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
n = n - 1
End If
n = n + 1
Loop Until val(strHold) > 0
SaveNumer2 = val(strHold)

End Function

*********************************************
...then call it like this:

? SaveNumer2("AS0090040101")
90040101

Note that 'real' numbers do not have preceding zeros, so
this function returns the numeric value as a long.

If you wanted to return just the first four real digits,
regardless of their placement in your input:
:
? val(Left(SaveNumer2("AS0090040101"),4))
9004

Note that this returns a long integer. Remove the Val()
and it'll return the same as a string.

HTH - Bob
 
Top