Solved

Converting numbers to words in an Access 2000 report

Posted on 2006-07-05
19
334 Views
Last Modified: 2006-11-18
I'm required to create a report that prints a number in 'thousands', 'hundreds', 'tens', 'units'. For example $104 must be written as:

Thousands Hundreds   Tens     Units
Zero          One           Zero     Four

I'm having a problem with leading zero(s) in this report. Any ideas how to script this function in the modules?
0
Comment
Question by:AXOHIT
  • 9
  • 7
  • 3
19 Comments
 
LVL 35

Accepted Solution

by:
Raynard7 earned 50 total points
ID: 17047832
I would create a vba function

where you pass in the colunn ie (1000) or (100) ...and the value of the number $104

Public Function getWords(columnNo As Long, value As Long) As String
    Select Case Right(CStr(value \ columnNo), 1)
        Case 0
            getWords = "zero"
        Case 1
            getWords = "one"
        Case 2
            getWords = "two"
        Case 3
            getWords = "three"
        Case 4
            getWords = "four"
        Case 5
            getWords = "five"
        Case 6
            getWords = "six"
        Case 7
            getWords = "seven"
        Case 8
            getWords = "eight"
        Case 9
            getWords = "nine"
    End Select
   
End Function

0
 

Author Comment

by:AXOHIT
ID: 17047875
Thanks for the input.

However, I already have this case structure in the module function. What I need to know is how to pad the leading zeroes.

As another example $1 must have leading 'zeros' in the thousands, hundreds and tens, so:

Thousands Hundreds   Tens     Units
Zero          Zero           Zero     One

The function you have given does not do this it will give blanks instead of the leading zeros as below:

Thousands Hundreds   Tens     Units
                                             One
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17047908
How are you calling this in your query? I have run it and the function returns "zero" if you call getWords(1000, 104) - I am thinking the problem may be how you are calling it
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17047909
You should be able to test this using the immediate window (ctrl+g) in the code window and typing

? getWords(1000, 104)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17048446
Are you wanting the columns returned distinctly as it appears in your example in the question?
Then, as stated you'd call the function for each position.

Although I'd suggest a change to the function to use this as the Select Case statement

Select Case Right(CStr(value \ (10 ^ (columnNo - 1))), 1)
0
 

Author Comment

by:AXOHIT
ID: 17048559
OK, thanks again for the reply. Below I've pasted the module script. Could you have a look and see if you can see what I'm doing wrong?

Option Compare Database
Option Explicit

Public Function ConvertCurrencyToWords(ByVal MyNumber)
   Dim Temp
   Dim Dollars, Cents
   Dim DecimalPlace, Count

   ReDim Place(9) As String
   Place(2) = " "  'Was " Thousand "
   Place(3) = " "   'Was " Million "
   Place(4) = " "   'Was " Billion "
   Place(5) = " "  'Was " Trillion "

   ' Convert MyNumber to a string, trimming extra spaces.
   MyNumber = Trim(Str(MyNumber))

   ' Find decimal place.
   DecimalPlace = InStr(MyNumber, ".")

   ' If we find decimal place...
   If DecimalPlace > 0 Then
      ' Convert cents
      Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
      Cents = ConvertTens(Temp)

      ' Strip off cents from remainder to convert.
      MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
   End If

   Count = 1
   Do While MyNumber <> ""
      ' Convert last 3 digits of MyNumber to English dollars.
      Temp = ConvertHundreds(Right(MyNumber, 3))
      If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
      If Len(MyNumber) > 3 Then
         ' Remove last 3 converted digits from MyNumber.
         MyNumber = Left(MyNumber, Len(MyNumber) - 3)
      Else
         MyNumber = ""
      End If
      Count = Count + 1
   Loop

   ' Clean up dollars.
   Select Case Dollars
      Case ""                                         'ADD 10 SPACES TO Dollars FIELD
         Dollars = "Zero              Zero            Zero         Zero           " 'was "No Dollars"
      Case "One"
         Dollars = "One          " 'was "One Dollar"
      Case Else
         Dollars = Dollars & " "                       'NOT IN USED
   End Select

   ' Clean up cents.
   'Select Case Cents
   '   Case ""
   '      Cents = " And No Cents"
   '   Case "One"
   '      Cents = " And One Cent"
   '   Case Else
   '      Cents = " And " & Cents & " Cents"
   'End Select

   ConvertCurrencyToWords = Dollars '& Cents - Removes the cents from the calc
End Function

Private Function ConvertHundreds(ByVal MyNumber)
   Dim Result As String

   ' Exit if there is nothing to convert.
   If Val(MyNumber) = 0 Then Exit Function

   ' Append leading zeros to number.
   MyNumber = Right("000" & MyNumber, 3)

   ' Do we have a hundreds place digit to convert?
   If Left(MyNumber, 1) <> "0" Then
      Result = ConvertDigit(Left(MyNumber, 1)) & " " 'was "hundred"
   End If

   ' Do we have a tens place digit to convert?
   If Mid(MyNumber, 2, 1) <> "0" Then
      Result = Result & ConvertTens(Mid(MyNumber, 2))
   Else
      ' If not, then convert the ones place digit.
      Result = Result & ConvertDigit(Mid(MyNumber, 3))
   End If

   'ConvertHundreds = Trim(Result)                     - REMOVE TRIM EXTRA SPACES
   ConvertHundreds = Result
End Function

Private Function ConvertTens(ByVal MyTens)
   Dim Result As String

   ' Is value between 10 and 19?
   If Val(Left(MyTens, 1)) = 1 Then
      Select Case Val(MyTens)
         Case 10: Result = "One  Zero"
         Case 11: Result = "One  One"
         Case 12: Result = "One  Two"
         Case 13: Result = "Thirteen"
         Case 14: Result = "Fourteen"
         Case 15: Result = "Fifteen"
         Case 16: Result = "Sixteen"
         Case 17: Result = "Seventeen"
         Case 18: Result = "Eighteen"
         Case 19: Result = "Nineteen"
         Case Else
      End Select
   Else
      ' .. otherwise it's between 20 and 99.
      Select Case Val(Left(MyTens, 1))           '-ADD  SPACES AFTER Result FIELD
         Case 2: Result = "Two          "
         Case 3: Result = "Three        "
         Case 4: Result = "Four         "
         Case 5: Result = "Five         "
         Case 6: Result = "Six          "
         Case 7: Result = "Seven        "
         Case 8: Result = "Eight        "
         Case 9: Result = "Nine         "
         Case Else
      End Select

      ' Convert ones place digit.
      Result = Result & ConvertDigit(Right(MyTens, 1))
   End If

   ConvertTens = Result
End Function

Private Function ConvertDigit(ByVal MyDigit)
   Select Case Val(MyDigit)                        ' - ADD  SPACES
      Case 0: ConvertDigit = "Zero         "
      Case 1: ConvertDigit = "One          "
      Case 2: ConvertDigit = "Two          "
      Case 3: ConvertDigit = "Three        "
      Case 4: ConvertDigit = "Four         "
      Case 5: ConvertDigit = "Five         "
      Case 6: ConvertDigit = "Six          "
      Case 7: ConvertDigit = "Seven        "
      Case 8: ConvertDigit = "Eight        "
      Case 9: ConvertDigit = "Nine         "
      Case Else: ConvertDigit = " "                 'NOT IN USED
   End Select
End Function
0
 

Author Comment

by:AXOHIT
ID: 17048616
LPurvis - 'Are you wanting the columns returned distinctly as it appears in your example in the question?'

Yes. I need the 'numbers in words' to match up with the relating unit columns as illustrated in the example in the question.

'Select Case Right(CStr(value \ (10 ^ (columnNo - 1))), 1)'

I'm not sure how to fit this in with the syntax I pasted above.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17049416
Right - and you're wanting to enter a single integer value and have it return then whole formatted block?
So you'd just call

ConvertCurrencyToWords(201)

and get

Thousands Hundreds   Tens     Units
Zero          Two           Zero     One

And are you using a fixed spaced font, like Courier, to display the results?
(Assuming so - with your apparent use of spacing to pad it out)
0
 

Author Comment

by:AXOHIT
ID: 17056018
LPurvis - I am not entering a single integer input; there are records in a table with is to be printed in a report which I have created.

So as you can see I've hard coded $0 to show:

Thousands Hundreds   Tens     Units
Zero          Zero           Zero     Zero    

However, currently $1 is appearing as:

Thousands Hundreds   Tens     Units
                                             one

which is without the leading zeroes. This is happening for all leading zeroes, they simply aren't showing us.

I'm not sure where in my script things are going wrong, any ideas?

Yes, I'm using a fix spaced font - though if you have a better solution I'm open to using something else rather than using spaces to pad out.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17056254
Well my first question is why are you having separate amounts calculated?
Do you not *want* to be able to pass an amount (less than 10,000) to a function and recieve your total back?

And the alternative is a function which considers only each placement of a character (much like suggested by Raynard7 originally).
So you'd have a column structure set up with the headings for Thousands Hundreds   Tens     Units and a control under each.
In the control you'd have
Thousands                         Hundreds                            Tens                                   Units
getWords(4,[FieldName])    getWords(3,[FieldName])     getWords(2,[FieldName])     getWords(1,[FieldName])

That way you can use whatever font you wish.
You'd just have to make that subtle ammendment to that function which I suggested. :-)
0
 

Author Comment

by:AXOHIT
ID: 17056846
OK, I think I'm understanding a little bit better.

I'm still having a problem though. I'll try and explain:

I have a series of numbers that differs on each page of a report that I have written. Each number must be printed in words and numberals. So lets say that I have a series of numbers known as 'xNumbers' appearing on different pages of my report:

25324
1008
19500
13520

In my report I have a single 'text block' with a 'control source'. The 'control source' is my module. My module's name is 'test'. So, in the text box I have written "=test([xNumbers])"

Above this test box I currently have the words:

Thousands  Hundreds   Tens     Units

So my current script (as posted) returns the numbers in words (upto 19000) in this text box. Anything over 19000 doesn't really work quite right (which I also need to fix), and if the number is in the 100's or tens then the leading zeroes do not print.

I've tested Raynard7's script and I can't get it working with my report as I need it to be able to use the 'xNumber's in the reports.

So, do I need to create a module for each of the units to be written? If so how do I get them into the column structure? Would I do this with say five or six text boxes under a sturcture like:

Hundreds Thousands   Ten Thousands Thousands  Hundreds   Tens        Units
Text box                     Text box           Text box    Text box     Text box  Text box

Each with a differnet function "=test1s([xNumbers])", "=test10s([xNumbers])", "=test100s([xNumbers])". etc.

What does my script need to look like in order to use the xNumbers I have?

My boss will be back next week so I'll be a bit clearer then. Until then have a good weekend and thanks for you help thus far.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17056872
Oh the weekend is a long way away yet ;-)

So xNumbers is a field from which you want to break out the relavent information yes?
I strongly urge you to drop your own function.
No offence - but it's inefficent and unnecessary (in that there's lots of somewhat repetative code).

The simple function outlined (with the one modification) should be enough.

Hundreds Thousands           Ten Thousands                   Thousands                          Hundreds                          Tens                                 Units
=getWords(6,[xNumbers])   =getWords(5,[xNumbers])   =getWords(4,[xNumbers])   =getWords(3,[xNumbers])  =getWords(2,[xNumbers])  =getWords(1,[xNumbers])

Does that not do what you require?
0
 

Author Comment

by:AXOHIT
ID: 17070744
I have dropped my current function as proposed and used the function suggested by Raynard with some modification on it and find that it work for my report.

Thanks.

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17071603
*Some* modification?
Have you been implementing the changes and suggestions as we've gone through here - or just continuously fiddling with it yourself until it suddenly worked for you - all by itself?
0
 

Author Comment

by:AXOHIT
ID: 17086980
I basically used Raynard's script and implemented some of your suggestions to get a basic structure. I then worked around with it by adding a length check as I only wanted one leading zero in the thousands column if the number was 999 or below. I did this because I was told to only use four columns: units, tens, hundreds, thousands.

Thank you both for your help. I'll be splinting the points.

I've posted the script below for other people's reference:

Public Function getWords(columnNo As Long, value As Long) As String
    Select Case Right(CStr(value \ columnNo), 1)
        Case 0
            getWords = "zero "
        Case 1
            getWords = "one  "
        Case 2
            getWords = "two  "
        Case 3
            getWords = "three"
        Case 4
            getWords = "four "
        Case 5
            getWords = "five "
        Case 6
            getWords = "six  "
        Case 7
            getWords = "seven"
        Case 8
            getWords = "eight"
        Case 9
            getWords = "nine "
    End Select
End Function

Public Function NumberToConvert(MyNumber As Long)
    Dim txtUni, txtTen, txtHun, txtTho, txtTenTho, txtHunTho
    Dim NumberLength As Integer
   
    NumberLength = Len(CStr(MyNumber))
    'Always Reported
    txtUni = getWords(1, MyNumber)
    txtTen = getWords(10, MyNumber) & "      "
    txtHun = getWords(100, MyNumber) & "      "
    txtTho = getWords(1000, MyNumber) & "      "
    'Only reported if Number of greater length
    If NumberLength > 4 Then
        txtTenTho = getWords(10000, MyNumber) & "  "
    End If
    If NumberLength > 5 Then
        txtHunTho = getWords(100000, MyNumber) & "  "
    End If
NumberToConvert = txtHunTho & txtTenTho & txtTho & txtHun & txtTen & txtUni
End Function
0
 

Author Comment

by:AXOHIT
ID: 17086989
Sorry, I didn't know how to split the points. My boss wrote the last message, he didn't know I've already awarded the points.

LPurvis thank you very much for your help. Sorry that I didn't know how to spit the points.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17087043
That's OK - but I am confused as to how you haven't needed that ammendment I suggested way back when.

i.e. using
Select Case Right(CStr(value \ (10 ^ (columnNo - 1))), 1)
0
 

Author Comment

by:AXOHIT
ID: 17088654
I think we might have taken care of that with the second part of the script though your code is cleaner - we did it our way so we could run the individual scripts on separate occasions.

But you know in the end, after all that, we don't need to change the numbers into words due to a legislative change by the government. So thanks for your help and thanks to the government who made it more a learning exercise and of absolute importance.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17088810
I hadn't spotted that you need to pass the column No as a power of 10 - then, of course, it will work.
My alternative is to just pass it as an integer placement.
Passing the exponent of course makes it pretty trivial inside the function.

Changing requirements huh?
Always have - always will.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now