Text To Numbers in Query

[wheatlandacctech]

Here is the function:

Public Function SayNo(ByVal N As Currency) As String

Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion

If (N = 0@) Then SayNo = "zero": Exit Function

Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1

If (N >= Trillion) Then
Debug.Print N
Buf = Buf & SayNoDigitGroup(Int(N / Trillion)) & " trillion"
N = N - Int(N / Trillion) * Trillion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Billion) Then
Debug.Print N
Buf = Buf & SayNoDigitGroup(Int(N / Billion)) & " billion"
N = N - Int(N / Billion) * Billion
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Million) Then
Debug.Print N
Buf = Buf & SayNoDigitGroup(N \ Million) & " million"
N = N Mod Million
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Thousand) Then
Debug.Print N
Buf = Buf & SayNoDigitGroup(N \ Thousand) & " thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= 1@) Then
Debug.Print N
Buf = Buf & SayNoDigitGroup(N)
End If

If (Frac = 0@) Then
Buf = Buf
ElseIf (Int(Frac * 100@) = Frac * 100@) Then
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 100@, "00") & "/100"
Else
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
End If

SayNo = Buf
End Function

Private Function SayNoDigitGroup(ByVal N As Integer) As String

Const Hundred = " hundred"
Const One = "one"
Const Two = "two"
Const Three = "three"
Const Four = "four"
Const Five = "five"
Const Six = "six"
Const Seven = "seven"
Const Eight = "eight"
Const Nine = "nine"
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False

Select Case (N \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select

If (Flag <> False) Then N = N Mod 100
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
SayNoDigitGroup = Buf
Exit Function
End If

Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "twenty": Flag = True
Case 3: Buf = Buf & "thirty": Flag = True
Case 4: Buf = Buf & "forty": Flag = True
Case 5: Buf = Buf & "fifty": Flag = True
Case 6: Buf = Buf & "sixty": Flag = True
Case 7: Buf = Buf & "seventy": Flag = True
Case 8: Buf = Buf & "eighty": Flag = True
Case 9: Buf = Buf & "ninety": Flag = True
End Select

If (Flag <> False) Then N = N Mod 10
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & "-"
Else
SayNoDigitGroup = Buf
Exit Function
End If

Select Case (N)
Case 0:
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & "ten"
Case 11: Buf = Buf & "eleven"
Case 12: Buf = Buf & "twelve"
Case 13: Buf = Buf & "thirteen"


Case 14: Buf = Buf & "fourteen"
Case 15: Buf = Buf & "fifteen"
Case 16: Buf = Buf & "sixteen"
Case 17: Buf = Buf & "seventeen"
Case 18: Buf = Buf & "eighteen"
Case 19: Buf = Buf & "nineteen"
End Select

SayNoDigitGroup = Buf

End Function

Then on the form, I have a field called TransAmount which is the field that has the number. Then I have a caption call lblNoToWords that I'm trying to display the number text in. Then in the Form_Current event I have the following text.

Private Sub Form_Current()
On Error GoTo Form_Current_Err:

' Calls SayNo Function
SayNo = TransAmount.Value
lblNoToWords.Caption = SayNo

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox$
Resume Form_Current_Exit


End Sub

That is what I have currently. I'm trying to display the number as text on a check entry form.

Thanks for all the help.

Joel Wiggers

[Micron]

That doesn't answer the question of what (exactly) you are passing to the function. I used this for an existing test form where in the current record the control contains 2300. Debug.print that I added to the code reports two thousand three hundred. The call in the immediate window was

sayno forms![frmNameHere].controlNameHere

[davegri]

You're calling the function improperly. Try below:

Private Sub Form_Current()
On Error GoTo Form_Current_Err:

' Calls SayNo Function
'SayNo = TransAmount.Value
lblNoToWords.Caption = SayNo(TransAmount)

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox$
Resume Form_Current_Exit

End Sub

[davegri]

In addition to the above revision, it appears that your error handler is wrong. Try this new revision:

Private Sub Form_Current()
    On Error GoTo Form_Current_Err
    ' Calls SayNo Function
    'SayNo = TransAmount.Value
    lblNoToWords.Caption = SayNo(TransAmount)
Form_Current_Err_Exit:
    On Error Resume Next
    Exit Sub
Form_Current_Err:
    Select Case Err
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Current" & "."
    End Select
    Resume Form_Current_Err_Exit
End Sub

[wheatlandacctech]

Thank you very much! That worked!

Joel Wiggers

[CJ_London]

or you could just have a textbox rather than a label with a controlsource of

= SayNo(TransAmount)

[Welshgasman]

I actually linked to the one that starts.

'This code was originally written by Joe Foster.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Joe Foster
' Convert a currency value into an (American) English string
Function English(ByVal N As Currency)

but they do look identical?

[CJ_London]

Perhaps, but the OP just said 'to pass a number to the function' but not idea which function - the one you pointed to (perhaps changed or not, who would know), val, cstr etc.

[Welshgasman]

Perhaps, but the OP just said 'to pass a number to the function' but not idea which function - the one you pointed to (perhaps changed or not, who would know), val, cstr etc.

They used the other function that was posted entirely within that link. With a quick glance, I could not see any difference between the two, other than the names were changed and the credits removed.?

I then found that the either function would return x/100 if you had a fraction of x ?
I've amended it as I would like it, but no point posting if not needed by OP?