Converting numbers into text

[howlettb]

I want to be able to print checks from my database, but I'm having trouble figuring out how to change numbers (1,245.65) to text (One thousand, two hundred forty five and 65/100). Is it possible to do this? If so, do I do it in the Check Report, or somewhere else? Any help would be greatly appreciated. Thanks Bev

 

[ersocia]

check this link: http://access.mvps.org/access/modules/mdl0001.htm

to use the code, copy and paste it into a module, and name it anything you want (other than the function names)

in a query, call it like this:

NewFieldName: English(CurrencyField)

where CurrencyField is the name of your, well, currency field.

((source: http://www.accessforums.net/access/numbers-words-11585.html))

 

[howlettb]

Thanks for your response. I actually already had that module - I'm just not sure how/where to use it. Let's say there is a CheckReport with a CurrencyNumber field ... and a CurrencyText field. I'm assuming that on the Check Report, I'd go to the "CurrencyText" field, and use this module as the Control Source?? I've gotten pretty good with creating databases, but I'm not a programmer, so I'm afraid I'm missing the "lingo". I really really appreciate your help!

 

[pr2-eugin]

Hi Bev, the module is a common module in the sense it can be called from anywhere or any function, so it depends on you.. Do you wish to generate the text equivalent of the amount after it has been entered into a field through a Form? If so then you have to use the TextField of the Amount field's after update., something along the lines of:

Private Sub [COLOR=Blue]amnt_TxtField[/COLOR]_AfterUpdate()
    [COLOR=Blue]amnt_InWord_FieldName[/COLOR] = English([COLOR=Blue]amnt_TxtField[/COLOR])
End Sub

Or do you want it in a Query? Then you have to use it as:

SELECT *, English([COLOR=Blue]amount_FieldName[/COLOR]) AS Words_Amount FROM [COLOR=Blue]yourTableName[/COLOR];

All highlighted places need to change according to your need.

 

[howlettb]

Hi Paul ...

I am SO sorry, but I am just not getting this. I created a field in my table called CurrencyText. Then I went to my form and in the Field named "CreditAmount" ... I went to after update ... and from there I'm lost. I know that I'm supposed to somehow type something in that will trigger the "CurrencyTextModule", but I can't seem to get it right. I really appreciate your patience with this!

 

[pr2-eugin]

No dont worry.. I hope you know how to get inside the VBA Editor.. So now in the after update event.. Just copy the following code into the Sub.. as

Private Sub CreditAmount_AfterUpdate()
    If Len(CreditAmount & "")>0 Then
        Me.CurrencyText = [COLOR="Red"]English[/COLOR](CreditAmount)
    End If
End Sub

The 'English' is the method that is used for calling the function to convert the currency to words.. If you pass English(100) it will return One Hundred exactly. The If is used just as a precaution to avoid trying to convert null values..
If you still find it hard, let me know I will try to post some screenshots.. as I do not have access at home it has to wait until tomorrow.
NOTE: However it is a very bad practice to have calculated values in the table.. So it is best used in Reports/Queries.

 

[howlettb]

screen shots would be great. Why is it bad practice to put it in the form. I'm not sure how to do it in a report. I can't express enough how much I appreciate your help. Thanks again!

 

[howlettb]

OMG!!!!! Paul ... I got it. I have NO idea how, and it's not doing it at the right time yet, but I'll figure that out tomorrow. THANK YOU!!!! I'll play around with having it do it in the report as opposed to the form tomorrow, so I'm sure I'll have more questions. I owe you BIG!

 

[pr2-eugin]

Well the main reason being.. If in case the value in the Field (which another field depends upon)changes unlike Excel; Access does not update it automatically.. Given an example in your case you have the field "CreditAmount" which today you have it as 95 so the CurrencyText which depends on it is set to 'Ninety Five'.. Days later you realize it was not 95 but 195.. So you change it in Table view, but this will not be updated in the CurrencyText.. It will still hold 'Ninety Five' which should have been 'One Hundred and Ninety Five'.. In other cases, sometime if the data is deleted, then this record will have nowhere to lookup to.. Thus leading into corruption.. Hope this clears your question..

 

[pr2-eugin]

Good for you Bev.. That sounds great.. Just let me know how you get along..