Someone else may have a more elegant way of doing this but here is one way.
Below is a Public function that you can put in a module and then call from
anywhere, passing the amount to be evaluated.
In addition to the variables defined in the function, plese note that at the
end of the function I am assigning the counts to public variables by adding
the new counts to the any existing counts. For this, I have defined the
variable you see at the end as public variables in the module. This will let
the accumumlate values. I tested this by calling the function from a button
on a form. I used code in the OnOpen event of the form to reset the public
variables that will hold the grand totals for each denomination.
Here is the function:
Public Function SetDemonination(Amount As Double)
Dim lngTwenties As Long
Dim lngTwentyAmt As Long
Dim lngTens As Long
Dim lngTensAmt As Long
Dim lngFives As Long
Dim lngFivesAmt As Long
Dim lngOnes As Long
Dim lngTwentyCnts As Long
Dim lngTwentyCntsAmt As Long
Dim lngFiveCnts As Long
Dim lngFiveCntsAmt As Long
Dim lngOneCnts As Long
Dim intDecmlPlc As Integer
Dim intCentsAmt As Integer
'calculate the number of 20's
If Format(Amount, "00") >= 20 Then
lngTwenties = Int(Format(Amount, "00") / 20)
lngTwentyAmt = lngTwenties * 20
End If
'calculate the number of 10's
If Format(Amount - lngTwentyAmt, "00") >= 10 Then
lngTens = Int(Format((Amount - lngTwentyAmt), "00") / 10)
Else
lngTens = 0
End If
lngTensAmt = lngTens * 10
'calculate the number of 5's
If Format(Amount - (lngTwentyAmt + lngTensAmt), "00") >= 5 Then
lngFives = (Amount - (lngTwentyAmt + lngTensAmt)) / 5
Else
lngFives = 0
End If
lngFivesAmt = lngFives * 5
lngOnes = Amount - (lngTwentyAmt + lngTensAmt + lngFivesAmt)
'Look for a decimal place
intDecmlPlc = InStr(Str(Amount), ".")
'Check to see that a decimal place was founc
If intDecmlPlc > 0 Then
'Convert cents
intCentsAmt = Left(Mid(Str(Amount), intDecmlPlc + 1) & "00", 2)
lngTwentyCnts = Int(intCentsAmt / 20)
lngTwentyCntsAmt = lngTwentyCnts * 20
If intCentsAmt - lngTwentyCntsAmt >= 5 Then
'get the Five cent count
lngFiveCnts = Int(intCentsAmt - lngTwentyCntsAmt) / 5
Else
lngFiveCnts = 0
End If
lngFiveCntsAmt = lngFiveCnts * 5
lngOneCnts = intCentsAmt - (lngTwentyCntsAmt + lngFiveCntsAmt)
Else
lngTwentyCnts = 0
lngOneCnts = 0
End If
'assign the counts of each demonination to global variables
lngTotalTwenties = lngTotalTwenties + lngTwenties
lngTotalTens = lngTotalTens + lngTens
lngTotalFives = lngTotalFives + lngFives
lngTotalOnes = lngTotalOnes + lngOnes
lngTotalTwentyCnts = lngTotalTwentyCnts + lngTwentyCnts
lngTotalOneCnts = lngTotalOneCnts + lngOneCnts
Debug.Print lngTotalTwenties & " 20's"
Debug.Print lngTotalTens & " 10's"
Debug.Print lngTotalFives & " 5's"
Debug.Print lngTotalOnes & " 1's"
Debug.Print lngTotalTwentyCnts & " 20 cents"
Debug.Print lngTotalOneCnts & " cents"
End Function
Here are the public variables that I defined in the module:
Public lngTotalTwenties As Long
Public lngTotalTens As Long
Public lngTotalFives As Long
Public lngTotalOnes As Long
Public lngTotalTwentyCnts As Long
Public lngTotalOneCnts As Long
Good luck with your project.
-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm