Module to convert total payout to bills and coins

[djnich]

I need a module or query that can convert a total dollar amount to report a
payout in currency. For example, a pay total of $34.35 would appear in the
report as one $20 bill, one $10 bill, four $1 bills, one quarter, and one
dime.

 

[djnich]

Wonderfull! Largest denomination= $50.
Denominations: $50, 20, 10, 5, 1, .50, .25, .10, .05, .01

djnich

 

[John W. Vinson]

FiftyNotes = Int(Dollars / 50)

Cool code! One trick to make it a bit shorter (and more obscure): the integer
divide operator does the same as the above.

FiftyNotes = Dollars \ 50

John W. Vinson [MVP]

 

[John W. Vinson]

There's ALWAYS more than one way to skin a cat!

There sure is... (my KalliCat objects strenuously, so on her behalf I'm
posting an alternative):

Public Function CalcChange(PayOutDue As Currency) As String
Dim TotalCents As Currency
Dim Sizes As Variant
Dim Denoms As Variant
Dim iSize As Integer
Dim HowMany As Integer
Dim OfWhat As String
Sizes = Array(10000, 5000, 2000, 1000, 500, 100, 25, 10, 5, 1)
Denoms = Array(" Hundred", " Fifty", " Twenty", " Ten", " Five", _
" One", " Quarter", " Dime", " Nickel", " Penny")
TotalCents = PayOutDue * 100
For iSize = 0 To 9
HowMany = TotalCents \ Sizes(iSize)
If HowMany > 0 Then
OfWhat = Denoms(iSize)
If HowMany > 1 Then
If Right(OfWhat, 1) = "y" Then
OfWhat = Left(OfWhat, Len(OfWhat) - 1) & "ies"
Else
OfWhat = OfWhat & "s"
End If
End If
CalcChange = CalcChange & HowMany & OfWhat & ", "
End If
TotalCents = TotalCents Mod Sizes(iSize)
Next iSize
CalcChange = Left(CalcChange, Len(CalcChange) - 2)
End Function

That's fun... thanks for the challenge!

John W. Vinson [MVP]

 

[Douglas J. Steele]

No offense, but that's a pretty limiting decision!

Using an array would make it much simpler tochange the code should the 50
cent piece be eliminated, and a 3 dollar bill added. No need to declare
different variables: you simply change the definition of the 2 arrays.

 

[John W. Vinson]

Using an array would make it much simpler tochange the code should the 50
cent piece be eliminated, and a 3 dollar bill added. No need to declare
different variables: you simply change the definition of the 2 arrays.

Yep: except for the 240 pence to the pound...

Public Function CalcChange(PayOutDue As Currency) As String
Dim TotalCents As Currency
Dim Dollars As Currency
Dim Cents As Currency
Dim Sizes As Variant
Dim Denoms As Variant
Dim iSize As Integer
Dim HowMany As Integer
Dim OfWhat As String
Denoms = Array(" Five Pound", " Two Pound", " Guinea", " Sovereign", _
" Half Sovereign", " Crown", " Half Crown", " Florin", " Shilling", _
" Sixpence", " Threepence", " Twopence", " Penny")
Sizes = Array(1200, 480, 252, 240, 120, 60, 30, 24, 12, 6, 3, 2, 1)
TotalCents = PayOutDue * 240
For iSize = 0 To UBound(Denoms)
HowMany = TotalCents \ Sizes(iSize)
If HowMany > 0 Then
OfWhat = Denoms(iSize)
If HowMany > 1 Then
If Right(OfWhat, 1) = "y" Then
OfWhat = Left(OfWhat, Len(OfWhat) - 1) & "ies"
Else
OfWhat = OfWhat & "s"
End If
End If
CalcChange = CalcChange & HowMany & OfWhat & ", "
End If
TotalCents = TotalCents Mod Sizes(iSize)
Next iSize
CalcChange = Left(CalcChange, Len(CalcChange) - 2)
End Function

John W. Vinson [MVP]

 

[Douglas J. Steele]

Yep: except for the 240 pence to the pound...

Nice one, guv'nor.

 

[John Marshall, MVP]

No pounds, hapennys or farthings?

John... Visio MVP

Denoms = Array(" Five Pound", " Two Pound", " Guinea", " Sovereign", _
" Half Sovereign", " Crown", " Half Crown", " Florin", " Shilling", _
" Sixpence", " Threepence", " Twopence", " Penny")

 

[John W. Vinson]

No pounds, hapennys or farthings?

John... Visio MVP

John W. Vinson [MVP]

 

[djnich]

John,
I've had to set this project aside for too long, now--back in the hunt. I
am a rookie at this! What do I need to put in my report as fields, to call
the amounts from the module into the report? .djnich