Module to convert total payout to bills and coins

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
I know how to do this! It'll take a little bit of time, but I've got a bit of
time this evening! What I need to know is what is the largest denomination of
bill you want to consider? $100 bill? $20 bill? What?

Do you want to include the $2 bill?

Do you want to include the half-dollar coin?

Let me know.

Linq

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

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
Wonderfull! Largest denomination= $50.
Denominations: $50, 20, 10, 5, 1, .50, .25, .10, .05, .01

djnich
 
Where PayOutDue is the field holding the amount of the payout you start with.
Because of the way this site wraps text, I've used the line continuation
symbol, the underline ( _ ) to, hopefully, display the final product
correctly here.

This code converts the initial input into cents

TotalCents = PayOutDue * 100

then calculates the the total dollars

Dollars = Int(TotalCents / 100)

then calculates the remaining cents

Cents = TotalCents Mod 100


'This assumes amount is input in dollars and cents i.e. 123.34
'***** Code Starts ********************
TotalCents = PayOutDue * 100

Dollars = Int(TotalCents / 100)
Cents = TotalCents Mod 100

FiftyNotes = Int(Dollars / 50)
CashLeft = Dollars Mod 50

TwentyNotes = Int(CashLeft / 20)
CashLeft = CashLeft Mod 20

TenNotes = Int(CashLeft / 10)
CashLeft = CashLeft Mod 10

FiveNotes = Int(CashLeft / 5)
CashLeft = CashLeft Mod 5

OneNotes = CashLeft

Quarters = Int(Cents / 25)
CoinsLeft = Cents Mod 25

Dimes = Int(CoinsLeft / 10)
CoinsLeft = CoinsLeft Mod 10

Nickles = Int(CoinsLeft / 5)
CoinsLeft = CoinsLeft Mod 5

Pennies = CoinsLeft

PayOutBillsAndCoins = "$50: " & FiftyNotes & " $20: " & TwentyNotes & _
" $10: " & TenNotes & " $5: " & FiveNotes & " $1: " & OneNotes & _
" Quarters: " & Quarters & " Dime: " & Dimes & " Nickles: " & _
Nickles & " Pennies: " & Pennies

End Sub
'********** End of Code *****************************************************

Post back if you have any questions/problems.

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

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
FiftyNotes = Int(Dollars / 50)
Click to expand...

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]
 
There's ALWAYS more than one way to skin a cat!
Click to expand...

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]
 
Sent my code and shut down, went to sleep, them woke up realizing I'd left
out the HalfDollar!

Revised code:

'This assumes amount is input in
'dollars and cents i.e. 123.34
TotalCents = Me.ChangeDue * 100

Dollars = Int(TotalCents / 100)
cents = TotalCents Mod 100

FiftyNotes = Int(Dollars / 50)
CashLeft = Dollars Mod 50

TwentyNotes = Int(CashLeft / 20)
CashLeft = CashLeft Mod 20

TenNotes = Int(CashLeft / 10)
CashLeft = CashLeft Mod 10

FiveNotes = Int(CashLeft / 5)
CashLeft = CashLeft Mod 5

OneNotes = CashLeft

HalfDollars = Int(cents / 50)
CoinsLeft = cents Mod 50

Quarters = Int(CoinsLeft / 25)
CoinsLeft = CoinsLeft Mod 25

Dimes = Int(CoinsLeft / 10)
CoinsLeft = CoinsLeft Mod 10

Nickles = Int(CoinsLeft / 5)
CoinsLeft = CoinsLeft Mod 5

Pennies = CoinsLeft

PayOutBillsAndCoins = "$50: " & FiftyNotes & " $20: " & TwentyNotes & _
" $10: " & TenNotes & " $5: " & FiveNotes & " $1: " & OneNotes & _
" HalfDollars: " & HalfDollars & " Quarters: " & Quarters & " Dime: " &
Dimes & " Nickles: " & _
Nickles & " Pennies: " & Pennies


Slick, John, but I swore after Cobol class that I'd never use an array again!
;0)> I also like to be able to understand the code I write down the road, so
I have to keep mine simple and non-cryptic!

My Sneaky Pie Brown sez "Hello!" to your KalliCat!

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

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
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.
 
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.
Click to expand...

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]
 
No pounds, hapennys or farthings?

John... Visio MVP

John W. Vinson said:
Denoms = Array(" Five Pound", " Two Pound", " Guinea", " Sovereign", _
" Half Sovereign", " Crown", " Half Crown", " Florin", " Shilling", _
" Sixpence", " Threepence", " Twopence", " Penny")
Click to expand...
 
No penny-farthings either...no wait...that's a bicycle, isn't it?

I was glad to see see there were more posts here, thinking, after the effort
we all put into these hacks, that the OP had finally come back! No such luck!

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

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
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
 
Back
Top