Need help changing a result in a Text Box!

[Bob]

--
After searching Google.groups.com and finding no answer, Bob Vance asked:

I have three boxes on a form
(a) Shows a percentage e.g. 12.5% (tbRate)
(b) Enter a figure 1000.00 (tbWithOutGST)
(c) Shows result a*b 1250.00(tbWithGST)

But want the result to be (a minus b) result to be 888.89
So Basically i want it in reverse enter with GST and get result minus GST

How would I go about changing this, Please

Thanks in advance.........Bob Vance

 

[Randy Harris]

--
After searching Google.groups.com and finding no answer, Bob Vance asked:

I have three boxes on a form
(a) Shows a percentage e.g. 12.5% (tbRate)
(b) Enter a figure 1000.00 (tbWithOutGST)
(c) Shows result a*b 1250.00(tbWithGST)

But want the result to be (a minus b) result to be 888.89
So Basically i want it in reverse enter with GST and get result minus GST

How would I go about changing this, Please

Thanks in advance.........Bob Vance

This formula will do that calculation:

c = b / (1 + (a / 100))

or it can be rewritten:

c = b * 100 / (100 + a)

HTH,
Randy

 

[Bob]

Where would I find the formula to change it, Thanks

 

[Randy Harris]

Where would I find the formula to change it, Thanks

It could be in a number of different places. Perhaps the most likely is in
the code behind that form. Check the AfterUpdate event for tbWithOutGST.
You might get a clue by watching the form to see what specific event causes
a value to appear in tbWithGST.

 

[Bob]

Found it how would I change it. Thanks Bob

It could be in a number of different places. Perhaps the most likely is
in
the code behind that form. Check the AfterUpdate event for tbWithOutGST.
You might get a clue by watching the form to see what specific event
causes
a value to appear in tbWithGST.

 

[Bob]

Sorry here it is:
Private Sub tbWithoutGST_AfterUpdate()
tbWithGST.value = funCalGST
End Sub

 

[Randy Harris]

Sorry here it is:
Private Sub tbWithoutGST_AfterUpdate()
tbWithGST.value = funCalGST
End Sub

Bob, it's getting its data from that function (funCalGST). Do a search for
that, then post the function.

Randy

 

[Bob]

Found it but it is being used for something else:
Can a new script be written?

Function funCalGST() As Currency
Dim sngGstPercentage As Single, recGSTOptions As ADODB.Recordset
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", cnnStableAccount, adOpenDynamic,
adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
'Exit Sub
End If

sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))
tbRate.value = sngGstPercentage * 100
funCalGST = (Nz(tbWithoutGST.value, 0) * Nz(tbRate.value, 0) / 100) +
Nz(tbWithoutGST.value, 0)
End Function

 

[Randy Harris]

Found it but it is being used for something else:
Can a new script be written?

Function funCalGST() As Currency
Dim sngGstPercentage As Single, recGSTOptions As ADODB.Recordset
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", cnnStableAccount, adOpenDynamic,
adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
'Exit Sub
End If

sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))
tbRate.value = sngGstPercentage * 100
funCalGST = (Nz(tbWithoutGST.value, 0) * Nz(tbRate.value, 0) / 100) +
Nz(tbWithoutGST.value, 0)
End Function

This function is designed to get the GST percent rate from a table, rather
than the text box control - tbRate. It has some serious problems, however.
Rather than risk using an obviously defective function, or attempting to fix
it, let's simply go back to the After update event and enter the proper
calculation there.

Change this:

Private Sub tbWithoutGST_AfterUpdate()
tbWithGST.value = funCalGST
End Sub

To:

Private Sub tbWithoutGST_AfterUpdate()
Me.tbWithGST = Me.tbWithOutGST / (1 + (Me.tbRate / 100))
End Sub

See if that doesn't get you the result that you want. BTW - assuming this
gets you the desired result, you might want to put the identical same
calculation in the AfterUpdate event for tbRate. That way, if someone
enters a value in tbWithoutGST before entering a GST rate, the calculation
will still be done.

Randy

 

[Bob]

Randy that worked fine but, If I change the tax rate on front form 12.5 to
10 the text box next to withgst and withoutgst dose not alter so i can not
get a 10% caluclation, Regards Bob

 

[Bob]

The box is tbRate, that is not changing from 12.5 to 10
Thanks Bob

 

[Bob]

Actually if I manually change tbRate box any % caluclation works but it does
not hold the % number when i close and open, Thanks Bob

 

[Bob]

I had to change the GST rate a bottom of invoice to change it from 12.5 to
10 and that changed my calculator, thank you very much for your help, Can I
make the without gst box so as you cant enter anything in to it?
Thanks Bob

 

[Randy Harris]

I had to change the GST rate a bottom of invoice to change it from 12.5 to
10 and that changed my calculator, thank you very much for your help, Can I
make the without gst box so as you cant enter anything in to it?
Thanks Bob

Bob, open the form in design view. Select the tbWithoutGST text box. Open
the properties page. Change the Locked Property to Yes. The text box will
appear normal on the form, but you won't be able to edit the value in it.

Randy

 

[Bob]

Thanks Randy ,having a problem with my Invoice system, seems to be one cent
more or less one cent out sometimes so if one account is $0.01 I have to
change the sum from say:
87.88 to 87..888 even though I have a debit of 87.88 and I enter 87.88,
really I shouldn't be able to enter ##.### ?
Thanks for your help, Bob

 

[Ed Warren]

I have run into this problem when I'm trying to build an application that
does calculations on currency then when I do a sum the results are 'off' by
..01 (actually the results are right but appear incorrect because the program
is keeping 32 decimal point accuracy and the the user is seeing only two
digit accuracy the result of:
1.011 x 10 =10.11
but to the user appears the result should be:
1.01 x10 = 10.10 and the 'correct result is 'wrong'

TwoDigit(1.011) x 10 = 10.10 (wrong but 'right') !!!

This code will work to keep everything to two digits.

Public Function TwoDigit(val As Currency) As Currency

Dim tempVal As Currency
tempVal = val * 100
tempVal = tempVal \ 1 'keep just the 'integer portion'
TwoDigit = tempVal / 100 'now divide and will have only two digit accuracy
returned.

End Function

Ed Warren

 

[Bob]

Thanks Ed, How do I go about putting this code in to my database and where,
TIA Bob

 

[Ed Warren]

Place the function in a module

then use it in your queries where you are doing calcualtions.

(a) Shows a percentage e.g. 12.5% (tbRate)
(b) Enter a figure 1000.00 (tbWithOutGST)
(c) Shows result a*b 1250.00(tbWithGST)

(c) result = TwoDigit(a*b)
or in a query
result: TwoDigit(a*b)
then when you sum(result) you get the 'expected' result.

Ed Warren

 

[Bob]

Ed How would I put this into my whole Access data base programme as it is a
Invoice and Payment that is coming up with the 0.01 or -0.001
Regards Bob Vance

 

[Ed Warren]

Without writing your application, I'm not sure I can be of much more more
help.

1. As I previously stated you put the function in a code module, so you can
use it in any form or query.
2. Anytime you do a calculation that you want to show at only two digit
accuracy you run it through the function to make sure you get only two
digits back

For example in your invoice you have : tbRate and tbWithOutGST
you want to get tbWithGST = tbRate*tbWithOutGST as a result in a
query/form
In a query you would enter
tbWithGST:twodigit(tbRate*tbWithOutGST)
now when you do a sum on tbWithGST you get the 'desired' result
for a report you would base the report the a
query like the one above.

in a form you would enter for the txtbox holding
the value =TwoDigit(tbrate*tbWithOutGST)

Ed Warren