MANIPULATE FIELD

[angie]

i have a field in a query consisting of both text and numbers.
sample field data : "ps-re 38107" "ps-bmw 39155" "ar- 46505888"
i want to manipulate the data (create field expression) in order to have
only the number contained in each field (i.e. without text and spaces)
i have tried the RightFunction in combination with the LenFunction but i
cannot get the desired result because the data do not have the same format
(pl check sample data stated above to see why i cannot use the
RightFunction).

is there something else i could do? pls help me!

 

[John Spencer]

Is there
-- always one space in the field
-- does the space always occur before the number characters
-- no characters after the number characters?

If so, then the following may work for you
Mid([SampleField], Instr(1,[SampleField]," ") + 1)

Otherwise, you will need a custom function. If you want ALL the number
characters in SampleField then that is one function, but if you have data
like
ps-3t 38107 and want 38107 returned and not 338107 then that "rule" needs
to be known.

The following will return ALL the number characters from any string. Paste
the function into a VBA module and save the module with a name OTHER than
fStripToNumbersOnly. You can use it in a query as follows

Field: OnlyNumbers: fStripToNumbersOnly([SampleField])

'========= Code Begins ===========
Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function
'========= Code ends ===========


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..