Add field in orders - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Add field in orders

Roger Strate

3 years ago

I am trying to add a new field in orders: "Deposit Amount" and Freight Charge on the order form. The current fields work just fine with the code =-Nz([DepositAmount])+Nz([FreightCharge])+[OrderDetailF].[Form]![SumExtPrice]. When I add the field netterms but when I add the field and edit the GrandTotal with this code: =-Nz([DepositAmount])-Nz([NetTerms])+Nz([FreightCharge])+[OrderDetailF].[Form]![SumExtPrice] I get the error "#Type" in the 2% NetTerm field and #Size in the GrandTotal field. The following is the code for the netterms field: =IIf([PaidDate]<=[OrderDueDate],[Total]*0.02,0). Any ideas why this isn't working. I wll send an image of my Order Form.

	Roger Strate OP @Reply 3 years ago

	Roger Strate OP @Reply 3 years ago

Kevin Robertson @Reply

3 years ago

The problem is you are creating a Circular Reference when you use OrderTotal in your IIF in the NetTerms field before it has been calculated and because of this the OrderTotal is also generating an error when you use NetTerms which can't be calculated because of the error.

I would add a Subtotal textbox which you can hide if you don't need to see it.

This is what I would put in each of the Control Sources:

Subtotal
=[OrderDetailF].[Form]![SumExtPrice]-Nz([DepositAmount])+Nz([FreightCharge])

NetTerms
=IIf([PaidDate]<=[OrderDueDate],[Subtotal]*0.02,0)

Total
=Nz([Subtotal])-Nz([NetTerms])

	Roger Strate OP @Reply 3 years ago
Thank you so much Kevin, that works perfectly. I appreciate your skills and time.

	Kevin Robertson @Reply 3 years ago
You're welcome.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View