Converting text string to a number in Access Queries

[jsg]

Does anyone know how to convert a text string into a real number? For
example I have a field that returns the value "SIX" but I need to convert it
into the number "6" to do a comparison with an integer field.

Thanks in advance for any help!

 

[Jim Burke in Novi]

Pretty sure Access doesn't a native function that converts from the word to
the number, though I guess it's possible someone's written one. If you just
have to worry about a very limited number of them, just create a global
function with a SELECT statement, otherwise good luck!:

Public Function WordToInteger(byval wordNumber as variant) as Integer

SELECT case
CASE "ONE"
WordToInteger = 1
CASE "TWO"
WordToInteger = 2

etc....

CASE ELSE
msgbox "Error: Could not determine the integer value of " &
wordNumber
WordToInteger = -1 (or whatever you want to do to denote a number
that
you don't have defined)
END SELECT

End FUnction

something along those lines.

 

[John W. Vinson]

Does anyone know how to convert a text string into a real number? For
example I have a field that returns the value "SIX" but I need to convert it
into the number "6" to do a comparison with an integer field.

Thanks in advance for any help!

What range of numbers do you need to deal with? You could create a little
two-field table with a number field NumValue and a textfield NumName:

1 ONE
2 TWO
3 THREE

etc. Join your (really really wierd) table to this by NumName to pick up the
NumVal.

This might get problematic if you need to deal with TWO MILLION, THREE HUNDRED
FORTY-EIGHT THOUSAND, EIGHT HUNDRED AND FIFTY...

 

[fredg]

Does anyone know how to convert a text string into a real number? For
example I have a field that returns the value "SIX" but I need to convert it
into the number "6" to do a comparison with an integer field.

Thanks in advance for any help!

Simple numbers, i.e. 0 to 9?
Probably the simplest method would be to create a table with 9
records.
MyNumber NumText
0 Zero
1 One
2 Two
.....etc ..
9 Nine


Then in your query where you wish to convert "Six" to 6, write:
NumberValueLookUp("[MyNumber]","TableName","[NumText] ] = '" &
[MyFieldName] & "'")

 

[John Spencer]

Here is a function that should convert a string of all words to a number
value. It only handles positive whole numbers up to 999 trillion.

It seems to work for everything I have tested it on. If it fails please
let me know what your input was and how it failed. This should work in
all versions of Access AFTER Access 97.

'============ Code begins ==================================

Public Function fStringWordsToNumber(ByVal strIN) As Variant
'Convert a string to a number
'One Million One thousand two hundred forty one > 1,001,241
Dim vArray(32, 1)
Dim vStr As Variant
Dim i As Integer
Dim LCurrent
Dim sCalc0 As String, sCalc1 As String


vArray(0, 0) = "zero": vArray(0, 1) = 0
vArray(1, 0) = "one": vArray(1, 1) = 1
vArray(2, 0) = "two": vArray(2, 1) = 2
vArray(3, 0) = "three": vArray(3, 1) = 3
vArray(4, 0) = "four": vArray(4, 1) = 4
vArray(5, 0) = "five": vArray(5, 1) = 5
vArray(6, 0) = "six": vArray(6, 1) = 6
vArray(7, 0) = "seven": vArray(7, 1) = 7
vArray(8, 0) = "eight": vArray(8, 1) = 8
vArray(9, 0) = "nine": vArray(9, 1) = 9
vArray(10, 0) = "ten": vArray(10, 1) = 10
vArray(11, 0) = "eleven": vArray(11, 1) = 11
vArray(12, 0) = "twelve": vArray(12, 1) = 12
vArray(13, 0) = "thirteen": vArray(13, 1) = 13
vArray(14, 0) = "Fourteen": vArray(14, 1) = 14
vArray(15, 0) = "Fifteen": vArray(15, 1) = 15
vArray(16, 0) = "Sixteen": vArray(16, 1) = 16
vArray(17, 0) = "seventeen": vArray(17, 1) = 17
vArray(18, 0) = "eighteen": vArray(18, 1) = 18
vArray(19, 0) = "nineteen": vArray(19, 1) = 19
vArray(20, 0) = "twenty": vArray(20, 1) = 20
vArray(21, 0) = "thirty": vArray(21, 1) = 30
vArray(22, 0) = "forty": vArray(22, 1) = 40
vArray(23, 0) = "fifty": vArray(23, 1) = 50
vArray(24, 0) = "sixty": vArray(24, 1) = 60
vArray(25, 0) = "seventy": vArray(25, 1) = 70
vArray(26, 0) = "eighty": vArray(26, 1) = 80
vArray(27, 0) = "ninety": vArray(27, 1) = 90
vArray(28, 0) = "hundred": vArray(28, 1) = 10 ^ 2
vArray(29, 0) = "thousand": vArray(29, 1) = 10 ^ 3
vArray(30, 0) = "million": vArray(30, 1) = 10 ^ 6
vArray(31, 0) = "billion": vArray(31, 1) = 10 ^ 9
vArray(32, 0) = "Trillion": vArray(32, 1) = 10 ^ 12

On Error GoTo Proc_Error
strIN = Replace(strIN, ",", "") 'strip out commas

If Len(strIN & vbNullString) = 0 Then
fStringWordsToNumber = Null 'Optionally set to zero
Else
'split string into words based on
vStr = Split(strIN, " ")

For i = LBound(vStr) To UBound(vStr)
LCurrent = fStringWordsToNumberSub(vStr(i), vArray)
If IsNull(LCurrent) = False Then

Select Case LCurrent
Case Is > 999
sCalc0 = sCalc0 & " +((" & sCalc1 & ") *" & LCurrent & ")"
sCalc1 = vbNullString
Case 100
sCalc1 = sCalc1 & "*" & LCurrent '& ")"
Case Is < 100
sCalc1 = sCalc1 & "+" & LCurrent
End Select
End If

Next i
' Debug.Print sCalc0, sCalc1

'Check to see if there is anything to calculate
If Len(Trim(sCalc0 & sCalc1)) = 0 Then
fStringWordsToNumber = Null
Else
fStringWordsToNumber = Eval(sCalc0 & sCalc1)
End If

End If 'Len(strIN & vbNullString) = 0

Exit Function

Proc_Error:
fStringWordsToNumber = Null
MsgBox Err.Number & ": " & Err.Description, , "fStringWordsToNumber"

End Function


Private Function fStringWordsToNumberSub(strVal, arrVals)
Dim lReturn
Dim i As Integer

lReturn = Null

For i = LBound(arrVals) To UBound(arrVals)
If strVal = arrVals(i, 0) Then
lReturn = arrVals(i, 1)
Exit For
End If
Next

fStringWordsToNumberSub = lReturn
End Function
'========= Code ends ================================


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

Does anyone know how to convert a text string into a real number? For
example I have a field that returns the value "SIX" but I need to convert it
into the number "6" to do a comparison with an integer field.

Thanks in advance for any help!

Simple numbers, i.e. 0 to 9?
Probably the simplest method would be to create a table with 9
records.
MyNumber NumText
0 Zero
1 One
2 Two
....etc ..
9 Nine


Then in your query where you wish to convert "Six" to 6, write:
NumberValueLookUp("[MyNumber]","TableName","[NumText] ] = '" &
[MyFieldName] & "'")