calculating customer change in 10's 5' etc

[kgraphics]

Hi, thank in advance for your help, I am trying to make a access program to keep track of the money in the Till. I am thinking of storing the number of 20' , 10' , 5' to 0.01 in the till in a table, and once the total of the change is calculated, check to see if there are any 20' in table field that holds the number of 20' etc. and tell the end user to give customer for exp if change was 35 to output give: 1 20, 1 10 and 1 5. I am new to access but have programmed before using flash actionscript. The programming is not the problem I am having but how to go about doing this. The first thing that came to mind was using if statement, but that wouls be alot of if' is there a way I can use some kind loop to handle this, and what kind loop would be best for this. here is a example of what I have in mind.

variable
dim the_change
dim div_amount
dim div_result
dim outputrslt

in the on click event of a button

the_change = the_changetxt.text

do while the_change > 0
if the_change >20 then
div_amount = 20
div_result = the_change / div_amout ' This will give the number of 20's in the change
elseif
....... ' the same thing for untill 0.01
end if
do while div_result >= 1
if chngtble(div_amount) > 1 then ' dont really know if this will work chngtble is the names of the fields that hold the number of 20' , 10' etc in the Till so the name would be like chngtbl20, chngtbl10 etc
chngtble(div_amount) = chngtble(div_amount) - 1
div_result = div_result -1
outputrslt = outputrslt + 1
else
changetxt(div_amount).text = outputrslt
exit do
end if
Loop
loop

Is there easier way then this to do this, I cant think of a way to end the first loop, it should end, but for some reason on some tests I did it doesnt?

what are your thoughts on this, I am sure there is a better way to do this and it have been done before.

thanks in advance

 

[the_net_2.0]

This is quite an interesting problem! I would bet that you could use a base increment. If you did use one, you could easily translate a currency amount into the base amount by simply moving the decimal place over. You might also be able to use non-currency number for the calculation and convert it at the end. This would be one idea for exact change:

public function mChange()
 
dim ctr as integer
dim pAmountBuy as currency
dim pAmountPaid as currency
dim aChangeGoal as currency
dim aChange as currency
dim cPoints as variant
 
aChange = 0
 
'Uncomment these below for execution
'pAmountPaid = Amount of Money given to Cashier
'pAmountBuy = Purchase Price
'aChangeGoal = pAmountPaid - pAmountBuy
 
cPoints = Array(50, 20, 10, 5, 1, .25, .10, .05, .01)
 
   do until aChange = aChangeGoal
 
      for ctr = lbound(cPoints) to ubound(cPoints)
         if cPoints(ctr) > aChangeGoal - aChange then
            goto NextCtr
         else
            while aChangeGoal - aChange => cPoints(ctr)
               aChange = aChange + cPoints(ctr)
            wend
         end if
NextCtr:
      next ctr
 
   loop
 
end function

I suppose if you wanted to list out what types of change to give, you could use a dynamic array inside of the While Loop to be resized whenever you add a denomination. It may have to be two dimensional array though...1 for the denomination and the other for the amount of denominations you are using.

 

[kgraphics]

This is what I came up with for now, but it doesn't work fully, for some reason it goes in tot he last loob only 2 times instead of 3 if the for example change is 50.75 for the sake of testing only, I made if statements for only 20 10 and .25 it is understandable if those work there rest should be just repeat.

Option Compare Database
Option Explicit
Dim the_change
Dim div_amount
Dim div_result
Dim outputrslt

Private Sub Command19_Click()
the_change = Me.thechangetxt
mystart:
Me.l02 = the_change
If the_change >= 20 Then
div_amount = 20
div_result = the_change / div_amount
ElseIf the_change >= 10 Then
div_amount = 10
div_result = the_change / div_amount
ElseIf the_change >= 0.25 Then
div_amount = 0.25
div_result = the_change / div_amount
Else
div_amount = 0
End If
If div_result > 1 Then
If div_amount = 20 Then
Me.txt20 = Me.txt20 + 1
Me.my20 = Me.my20 - 1
div_result = div_result - 1
the_change = the_change - div_amount
GoTo mystart
ElseIf div_amount = 10 Then
Me.txt10 = Me.txt10 + 1
Me.my10 = Me.my10 - 1
div_result = div_result - 1
the_change = the_change - div_amount
GoTo mystart
ElseIf div_amount >= 0.25 & div_amount < 1 Then
Me.txt025 = Me.txt025 + 1
Me.my025 = Me.my025 - 1
div_result = div_result - 1
the_change = the_change - div_amount
Me.Text22 = Me.Text22 + 1
GoTo mystart
End If
Else
Exit Sub
End If
End Sub

if change was 50.75 the result should be 2 20', 1 10' and 3 quarters, it gets the 2 20' and 1 10' but the quarters it only reads 2 instead of 3

Anyone can anyone see why that is?

thanks

 

[kgraphics]

This is quite an interesting problem! I would bet that you could use a base increment. If you did use one, you could easily translate a currency amount into the base amount by simply moving the decimal place over. You might also be able to use non-currency number for the calculation and convert it at the end. This would be one idea for exact change:

public function mChange()
 
dim ctr as integer
dim pAmountBuy as currency
dim pAmountPaid as currency
dim aChangeGoal as currency
dim aChange as currency
dim cPoints as variant
 
aChange = 0
 
'Uncomment these below for execution
'pAmountPaid = Amount of Money given to Cashier
'pAmountBuy = Purchase Price
'aChangeGoal = pAmountPaid - pAmountBuy
 
cPoints = Array(50, 20, 10, 5, 1, .25, .10, .05, .01)
 
   do until aChange = aChangeGoal
 
      for ctr = lbound(cPoints) to ubound(cPoints)
         if cPoints(ctr) > aChangeGoal - aChange then
            goto NextCtr
         else
            while aChangeGoal - aChange => cPoints(ctr)
               aChange = aChange + cPoints(ctr)
            wend
         end if
NextCtr:
      next ctr
 
   loop
 
end function

I suppose if you wanted to list out what types of change to give, you could use a dynamic array inside of the While Loop to be resized whenever you add a denomination. It may have to be two dimensional array though...1 for the denomination and the other for the amount of denominations you are using.

Thanks, I am going to test this, didn't think of arrays at all, I just posted what I have so far, but as you can see it will be a long code, yours is much cleaner.

 

[Poppa Smurf]

This function will calculate your change


Function change_calc_case(change_amount As Currency)
Dim dollar_100 As Integer
Dim dollar_50 As Integer
Dim dollar_20 As Integer
Dim dollar_10 As Integer
Dim dollar_5 As Integer
Dim dollar_1 As Integer
Dim cent_50 As Integer
Dim cent_20 As Integer
Dim cent_10 As Integer
Dim cent_5 As Integer
Dim cent_1 As Integer
Dim temp_amount As Currency
temp_amount = change_amount
If temp_amount > 0 Then
Do While temp_amount > 0


Select Case temp_amount

Case Is >= 100
dollar_100 = Int(temp_amount / 100)
temp_amount = temp_amount - (dollar_100 * 100)

Case Is >= 50
dollar_50 = Int(temp_amount / 50)
temp_amount = temp_amount - (dollar_50 * 50)

Case Is >= 20
dollar_20 = Int(temp_amount / 20)
temp_amount = temp_amount - (dollar_20 * 20)

Case Is >= 10
dollar_10 = Int(temp_amount / 10)
temp_amount = temp_amount - (dollar_10 * 10)

Case Is >= 5
dollar_5 = Int(temp_amount / 5)
temp_amount = temp_amount - (dollar_5 * 5)

Case Is >= 1
dollar_1 = Int(temp_amount / 1)
temp_amount = temp_amount - (dollar_1 * 1)

Case Is >= 0.5
cent_50 = Int(temp_amount * 100 / 50)
temp_amount = temp_amount - (cent_50 * 0.5)

Case Is >= 0.2
cent_20 = Int(temp_amount * 100 / 20)
temp_amount = temp_amount - (cent_20 * 0.2)

Case Is >= 0.05
cent_5 = Int(temp_amount * 100 / 5)
temp_amount = temp_amount - (cent_5 * 0.05)

Case Is >= 0.01
cent_1 = temp_amount * 100

'no more change
temp_amount = 0

Case Else
MsgBox "breakdown error"
End Select

Loop
Else
MsgBox ("correct amount tendered")
End If


End Function

The input to the function is change_calc_case(change_amount As Currency) e.g. change_calc_case(186.86). This will give the breakdown for $186.86. Some of the denominations may not used in your currency. Make sure the case statements order is highest denomination to the lowest denomination.

 

[gemma-the-husky]

i think you could do this recursively with a bit of playing - and that would be the shortest code, once you got it correct.

the change is the change less the highest coin that can be used, until all the change is made. so change(41p) = 20p+change(21p) etc

slight technical probelms though- if you need 41p change, and have only 1 20p in the till, it might be tricky.

 

[kgraphics]

Thank you guys for the help, ima test both codes, like I said I am new to vba and access, Poppa Smurf code I can understand but the_net_2.0 code well I will have to learn few things about vb coding before I can understand, I never used for loop like that before, so I dont understand how it would work and how to get the output to print in a text box labeled the money to give (etc 20' ).

i think you could do this recursively with a bit of playing - and that would be the shortest code, once you got it correct.

the change is the change less the highest coin that can be used, until all the change is made. so change(41p) = 20p+change(21p) etc

slight technical problems though- if you need 41p change, and have only 1 20p in the till, it might be tricky.

that would be the easy part, but it will need some work, and some 'if statements' to check what is in the till before asking the cashier to give it, there is a cashier table that keeps the number of each currency in the till. and once the cashier takes money out to give as a change, it will be subtracted from the field in the cashier table.

 

[kgraphics]

This function will calculate your change

Thank soo much man, that was amazing, how easy it was and how hard I was making it, your code WORKS and thanks to you my nightmare is over, and it reads like plain English for a noob like me. Thank you for your time, and thanks to the owners of this amazing website.

 

[Poppa Smurf]

Glad to assist.

The attached example uses arrays. When you open the database a form is displayed to enter the number for each denominations in the till ( your database could populate these fields). Enter the sale price and the amount tendered. Click on the Display Breakdown and the change breakdown will be displayed in the Change fields.

If there is insufficent change in the till (not the till total value) for the change fields then a message is displayed and change fields show 0.

 

[kgraphics]

Thank you, you didnt need to do all that, I already started using the function and it works just fine with my database. Thanks for taking the time to do that, I am sure some other noob like me one day will do a search on this forum like I have been doing for the past 3 days before I posted the question and will find this post helpful like I did.