Percentage Discount Calculation

[scouser]

I have a discount calculation field. However when I input say value 1 for 1% it adds 100% 10 = 1000% etc..... the code is as follows:

Private Sub Discount_AfterUpdate()
With Me
Dim pcnt As Integer
Dim intTot As Currency

If .Discount > 0 Then
pcnt = (.ServicePrice * .Quantity) * .Discount
.LineTotal = (.ServicePrice * .Quantity) - pcnt
Else
.LineTotal = .Quantity * .ServicePrice
End If
DoCmd.RunCommand acCmdSaveRecord
End With
intTot = DSum("LineTotal", "[tblEnquiryDetails]", "EnquiryID = Forms!frmEnquiries!EnquiryID")
If Forms!frmEnquiries!VatAdd = False Then
Forms!frmEnquiries!Total = intTot
Forms!frmEnquiries!Vat = "0"
Forms!frmEnquiries!TotalGross = intTot
Else
Forms!frmEnquiries!Total = intTot
Forms!frmEnquiries!Vat = intTot * 0.175
Forms!frmEnquiries!TotalGross = (intTot * 0.175) + intTot
End If
End Sub

Any ideas?
Cheers,
Phil.

 

[GumbyD]

It looks to me like you need to divide the discount value by 100:

pcnt = (.ServicePrice * .Quantity) * (.Discount/100)


GumbyD

 

[scouser]

Error in calculation

Thanks Gumby. You were right but alas I have a calculation error + display error.

If Unit Price = £15 Quantity * 2 LineTotal = £30. If I add discount of 10% the Discount field reads 1000% yet deducts £3 which is correct - LineTotal = £27
However if Unit Price = £7.50 Quantity * 2 LineTotal = £15 + 10% (again reads 1000%) discount - LineTotal = £13 which is wrong?

Questio is then how can I get display to show 10% not 1000% and how can I get it to calculate decimal values from the code listed in the first thread? Should I change Integer to float? (or VB equivalent?).

Cheers,
Phil.

 

[Rich]

1 is a 100%, .1 is 10%, Integer will convert .1 to 1, change the field to double or single

 

[GumbyD]

Since you are working with currency values in the table set the data type to currency for the currency field and set the discount field to a doucle. Then on the form use the properties to set the display the way you would like.

GumbyD

 

[scouser]

Cheers Guys

Still having problems here! Now it won't even calculate the discount!! I have no idea what I have done!!!!!!
Silly me.....................
Cheers,
Phil.

 

[GumbyD]

One thing to look at - in your code you

Dim pcnt As Integer

This should be

Dim pcnt As Double

GumbyD

 

[scouser]

Thanks

Gumby thanks. I managed to see the error of my ways!! I have another issue! I have a button on my Order form that calls a Macro to print an nvoice report. The problem is it only calls the first Order entered not the current order on screen + doesn't update any changes? Any thoughts?
Many thanks for your time.
Phil.

 

[GumbyD]

Check the Macro for criteria. On the bottom of the screen in the Action Arguments there is a Where Condition argument. You might not have it set correctly to pull the data from the form.

GumbyD

 

[scouser]

Where Condition

Gumby, can you post your brain! Ha! Ha! I have no idea what to add to the where statement to ensure the most up to date order is printed! I have no idea how the expression builder works (can you explain this). Is it quite simple (like me). I really would appreciate any help as I need to be progressing faster than I am.
Cheers,
Phil.

 

[GumbyD]

It should look something like the following:

[InvoiceNumber]=[Forms]![OrderForm]![InvoiceNumber]

The syntax is:

[Field Name From Form you are Opening] = [Forms]![Form Your Field value Lives On]![Field Name on Form Where Value is Coming From]

I hope that makes sense!

Good Luck!

GumbyD

 

[scouser]

Erm.........

I have an order form (which is based a query qryOders + a sub form based on qryServices). The report is based on qryInvoiceQuery (which is based on tblCustomers, tblOrders, tblOrderDetails, tblEmloyees, tblServices).

I have a button on the order form called Print Invoice that should print the current invoice? Does this tie up with your previous thread??
Cheers,
Phil.

 

[GumbyD]

I think what I would do in your case is put the subform value that you need as criteria on the invoice report query. So you need to figure out what values on the form as criteria give you the unique invoice that you want and then do form calls in the query criteria for those fields on the form. Then your button can just be an open report action and the query will handle the criteria for you.

GumbyD

 

[scouser]

Cheers

Thanks for all your thoughts Gumby. I feel my DB is flawed!! I can't code so there is very little error checking, validation..etc...
I will put your thoughts into action tomorrow.
Cheers,
Phil.