Balance Remaining = 0.01p instead of 0 1

[SamDemon]

I have created a report using a query. The report is used as a VAT invoice. The problem that I am having is that the final balance is reading -£0.01 instead of £0.00.

The query is laid out as follows (Values in brackets):-

SingleNetFare:[SingleNet]+(([CommissionValue]+[ExtraCommission])-[CommissionDiscount]) - (£101.00)

SingleTax- (£46.40)

TicketValue- (£7.50)

SingleNetTotal: [SingleNetFare]+[SingleTax]+[TicketValue]- (£154.90)

TotalNetFare:[SingleNetFare]*[PAX]- (£1,414.00)

TotalTax: [SingleTax]*[PAX]- (£649.60)

TotalTicket: [TicketValue]*[PAX]-([TicketDiscount]*[PAX])- (£105.00)

NetTotal: [TotalNetFare]+[TotalTax]+[TotalTicket]- (£2,168.60)

CancellationFees- (£0.00)

Ammendments- (£25.00)

TotalNet: [NetTotal]+[CancellationFees]+[Ammendments]+[LossDepositTotal]- (£2,193.60)

TotalTicketVAT: [TotalTicket]*0.175 - (£18.38.00)

CompleteNetFare: [TotalNet]+[TotalTicketVAT]- (£2,211.98)

InitialDeposit- (£350.00)

BalanceDue: [CompleteNetFare]-[InitialDeposit]- (£1,861.98)

LossDepositPax- (£0.00)

LossDepositAmmount - (£0.00)

LossDepositTotal: [LossDepositPAX]*[LossDepositAmmount]- (£0.00)

BalancePayment - (£1,861.98.00)

FinalBalance: [BalanceDue]-[BalancePayment]- (£0.01)

The PAX value for this invoice was 14. All the figures shown above in brakets are what came out on the report. I think i can see where the error is occuring, but am not sure how to resolve it.

When I went through each calculation, I found that the TotalTicketVAT returned the value as shown above (£18.38) when infact the correct value should be £18.375. Access has rounded this value up here, but rounded it down again in the FinalPayment. How would I go about fixing this?

Thanks in advance!

Sam

It's just common sense, shame sense isn't common!

 

[lupins46]

You need to round the VAT amount to 2dp.
Otherwise there are extra decimals in your VAT result which you are hiding in your formatting.

Round([TotalTicket]*0.175,2)

 

[SamDemon]

Thank you very much!!

It's just common sense, shame sense isn't common!