Subform (Datasheet) Calculation (Syntax) Issues

[JK]

I have a subform displayed in Datasheet view with the followign fields:
Product Number (cbo), Product Name (cbo), Quantity, Discount %, Unit Price,
SupplierID, and Product Number (Ref.)

I want the user to select an item using the pull-down menu, enter a quantity
and have the subform calculate the amount charged based on the discount
that's stored elseware, the unit price and the quantity.

This is the formula I have and it's not working for some reason:
=IIf(IsNull([Quantity]),0,(Sum(([Quantity]*[Unit
Price])-([Quantity]*[Discount]*100)/100*[Quantity])))

I'm getting an #ERROR in all fields...

I then must sum all of the values that come from the calculation above to
display on the main form. Totaling the "ExtendedPrice" is where this issue
began... For some reason I can't seem to make it work...

Any help would be greatly appreciated. If you more details are required, let
me know and I'll send them along... Thx so much!!

 

[Minton M]

I have a subform displayed in Datasheet view with the followign fields:
Product Number (cbo), Product Name (cbo), Quantity, Discount %, Unit Price,
SupplierID, and Product Number (Ref.)

I want the user to select an item using the pull-down menu, enter a quantity
and have the subform calculate the amount charged based on the discount
that's stored elseware, the unit price and the quantity.

This is the formula I have and it's not working for some reason:
=IIf(IsNull([Quantity]),0,(Sum(([Quantity]*[Unit
Price])-([Quantity]*[Discount]*100)/100*[Quantity])))

I'm getting an #ERROR in all fields...

I then must sum all of the values that come from the calculation above to
display on the main form. Totaling the "ExtendedPrice" is where this issue
began... For some reason I can't seem to make it work...

Any help would be greatly appreciated. If you more details are required, let
me know and I'll send them along... Thx so much!!

A calculated form on a datasheet could do this for you, but I would
suspect a continuous form would give you more control since you're
doing lookups and calculations in other places. The #ERROR you're
seeing could be caused by having control names that have the same
names as fields (which is a perennial Access evil).

If you want to keep using the datasheet idea, make sure the field
names and control names are completely different. Then you could solve
the problem piece by piece - is a calculated field causing an error,
is a summation field causing an error, etc.

Hope this helps,
James