How do I incorporate this VBA into my database

[pieface]

In the database I'm building I need to change numbers into words (eg 200 into 'two hundred'), I have found a small Access module that does it great which works from a form. My problem is I want it to either work from a query or a report, not from a form. The form has a section of code as an event in the 'input' field which I guess is moving the data into the main module code and back again, after conversion, to the 'result' field of the same form. Do I need to transfer/edit this small section to somewhere else? If so where? I have enclosed the small section. [EnterDollars] is the label for the 'input' field and [ViewText] is the label for the 'result' field. Any help would be warmly received.

Option Compare Database   'Use database order for string comparisons
Option Explicit

Private Sub EnterDollars_AfterUpdate()

Dim Indollars As String
Indollars = Me![EnterDollars]
Me![ViewText] = ConvertToText(Indollars)
Forms![TestIt]![EnterDollars].SetFocus

' Me![ViewText] = "asdfas"
' ConvertToText (Me![EnterDollars])

End Sub

 

[grnzbra]

ConvertToText seems to be a function that converts numbers to text. Try just setting whatever you want equal to that function.

 

[thornmastr]

Well, it looks to me like the function with the power is called ConvertToText which is expecting a string as input. Since this function must now be made available to forms and queries (remember the driving force of a report is the query behind it).

You want to physically remove this function from the form level to the module level, so you would create a module, copy the function into the module and make sure it is declared as a Public function. Hopefully the function has no direct form interaction, but is only called from a forms underlying logic. If it does have direct dependence on something in the form you are going to have to get rid of that while insuring the global function works. Since the function is now defined at the global level when your form needs to call it, it will find it.

Both form, reports, and queries can reference global functions.

Now, lets assume you need a query which returns employee name, yearly gross as a number, and you also want in that query, the number in its alpha form.

Select empname,yrlygross,converttotext(str((yrlygros)) as stringgross from Yourtable

Robert Berman

 

[pieface]

Thanks for the help folks, it's still not working but I think you've managed to make some sense of my garbled question. thornmastr,your assumed example is almost exactly what I'm trying to achieve and yes ConvertToText is the main module that does all the work. I've tried adding it into my query, so far without success, it keeps giving me a 'Bad Dollars' error, which is generated from the module, so I assume I am not giving it the string it wants, how it wants. Would it be clearer for you if I show the start of main module? Here it is anyway.
This is obviously only the first section, but it might make it clearer. (God, I wish VBA wasn't so much Greek)

Option Compare Database   'Use database order for string comparisons
Option Explicit

Function ConvertToText(Indollars As String)

' Use:
'   Pass a dollar amount as a string -
'   Function will return a Text (English) version suitable for check printing
'
'   Examples of allowable formats
'       $356.72   871.09    8.40    9518    $769    829.8   $9012   $1.5
'
'   Limitations
'       Amount must be equal or greater than $1.00
'       Amount must be equal or less than $99999.99
'       Must be a completely numeric string (including no ,'s)
'       No more than two digits to the right of decimal point
'       No leading zeroes
'
' -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    
    Dim cvtd_Tmp As String, cvtd_Cents As String
    Dim cvtd_CheckText As String
    Dim cvtd_DP As Integer, cvtd_L As Integer, cvtd_NumDigits As Integer
    
    On Error GoTo BadInDollars
    cvtd_Tmp = Trim$(Indollars)
    If Left$(cvtd_Tmp, 1) = "$" Then cvtd_Tmp = Mid$(cvtd_Tmp, 2)
    If Left$(cvtd_Tmp, 1) = "0" Then GoTo BadInDollars

    GoSub cvtd_CheckDP           ' Check the decimal point placement
    GoSub cvtd_Stripcvtd_Cents        ' Seperate the cvtd_Cents portion
    cvtd_NumDigits = Len(cvtd_Tmp)    ' Save number of digits left of dec pt

' Process 1 - 9 --------------------------------------------
    If cvtd_NumDigits = 1 Then
        cvtd_CheckText = cvtd_ConvertUnits(cvtd_Tmp) & cvtd_Cents
        GoTo cvtd_ConversionCompleted
    End If

 

[pieface]

Mr Berman, you are a star. It would have worked just as you first explained if I weren't a complete halfwit. Thanks a 1,000,000 (Correction that is now 'Million')

 

[grnzbra]

try running the following sub in your code

public sub DollarFuncTest()

Dim desiredoutput as string

desiredoutput = ConvertToText("$100.00)

End Sub

Put a breakpoint at the equation line and run. See if the value of the variable is correct when the code stops. If it isn't then step through the function if you can get at it. Once you've got this running, then try adding a field to your query such as
TextBucks: = ConvertToText(NameOfFieldYouAreConverting)

 

[thornmastr]

It looks as if you are sending convert_to_text a string beginning with either a zero or commas in your string because that is what will trigger your error. It is also expecting a string of type $####0.00.Notice, no commas allowed, therefore, lets give it what it wants.

Now, I am assuming that the monetary value in your table is being maintained as currency,

So what you want to do, if the variable you are passing to convert_to_text is called money, then what you might want to try is Convert_to_text(format(money,"$####0.00”)).

Let me know if that works a bit better.

Robert Berman

 

[grnzbra]

OOPs, there should have been quotation marks on both sides of $100.00 as in "$100.00".

 

[pieface]

I think I got my reply in just before you two, it works a treat now. Thanks for the help. I just need to sort out now a minor glitch, the module is displaying some of the numbers incorrect by a penny, I assume the number string from the query is rounding up and the module is rounding down or some such thing. My eyes are now square, I'm hitting the sack. Thanks again folks.