Need assistance finding code error CALCULATION #Error

[newbie4access]

This works ...

Item Expense

=(Nz([AcqPricePd],0)*Nz([AcqQuan],0))+Nz([AcqMiscFeePd],0)+Nz([AcqTaxPd],0)+Nz([AcqShipgPd],0)-Nz([AcqDiscAmount],0)

This works ...

Item Sold

=-(Nz([ElimRelistFees],0)*Nz([ElimQuanXrelisted],0))+Nz([ElimPrice],0)-Nz([ElimListFees],0)-Nz([ElimSoldFees],0)-Nz([ElimPkgExp],0)+Nz([ElimShipChrgd],0)-Nz([ElimShipCost],0)-Nz([ElimMiscExpFees],0)

This works ...

Item Profit or Loss

=-((Nz([AcqPricePd],0)*Nz([AcqQuan],0))+Nz([AcqMiscFeePd],0)+Nz([AcqTaxPd],0)+Nz([AcqShipgPd],0)-Nz([AcqDiscAmount],0))+(-(Nz([ElimRelistFees],0)*Nz([ElimQuanXrelisted],0))+Nz([ElimPrice],0)-Nz([ElimListFees],0)-Nz([ElimSoldFees],0)-Nz([ElimPkgExp],0)+Nz([ElimShipChrgd],0)-Nz([ElimShipCost],0)-Nz([ElimMiscExpFees],0))

This works ...

Total Item Expenses

=Sum((Nz([AcqPricePd],0)*Nz([AcqQuan],0))+Nz([AcqMiscFeePd],0)+Nz([AcqTaxPd],0)+Nz([AcqShipgPd],0)-Nz([AcqDiscAmount],0))

This works ...

Total Item Sales

=Sum(-(Nz([ElimRelistFees],0)*Nz([ElimQuanXrelisted],0))+Nz([ElimPrice],0)-Nz([ElimListFees],0)-Nz([ElimSoldFees],0)-Nz([ElimPkgExp],0)+Nz([ElimShipChrgd],0)-Nz([ElimShipCost],0)-Nz([ElimMiscExpFees],0))

But, this does NOT work (below) :banghead:

Total Profit or Loss

=Sum(+Nz([AcqDiscAmount],0)+Nz([ElimPrice],0)+Nz([ElimShipChrgd],0)-(Nz([AcqPricePd],0)*Nz([AcqQuan],0))-Nz([AcqMiscFeePd],0)-Nz([AcqTaxPd],0)-Nz([AcqShipgPd],0)-Nz([ElimListFees],0)-(Nz([ElimRelistFees],0)*Nz([ElimQuanXreListed],0))-Nz([ElimSoldFees],0)-Nz([ElimPkgExp],0)-Nz([ElimShipCost],0)-Nz([ElimMiscExpFees],0))

here it is easier to read

=Sum(
+Nz([AcqDiscAmount],0)
+Nz([ElimPrice],0)
+Nz([ElimShipChrgd],0)
-(Nz([AcqPricePd],0)*Nz([AcqQuan],0))
-Nz([AcqMiscFeePd],0)
-Nz([AcqTaxPd],0)
-Nz([AcqShipgPd],0)
-Nz([ElimListFees],0)
-(Nz([ElimRelistFees],0)*Nz([ElimQuanXreListed],0))
-Nz([ElimSoldFees],0)
-Nz([ElimPkgExp],0)-Nz([ElimShipCost],0)
-Nz([ElimMiscExpFees],0)
)

The weird thing is, is that it makes the previous Total calculations have #Errors too.

I've been wracking my brain for a week to no avail. :banghead:
Any help appreciated.

 

[MarkK]

I would do this in VBA, not in a ControlSource. In VBA you have rich debugging tools, and you can see the whole expression, and you can calculate it incrementally, and once you've solved it in VBA, other forms and reports can use the same math, and so on, and so on.

I know this doesn't directly address your issue, but something to think about as your system grows.

 

[plog]

First, I'm pretty sure your table is improperly structured. You shouldn't have a column for each possible fee/cost, especially since not all will be used. Instead you need a whole new table for your fees/costs:

Fees
Fee_ID, autonumber, primary key
ForeignKeyID, number, foreign key to table this data is currently in
Fee_Amount, number, amount of fee
Fee_Type, text, this will hold what are currently your field names for all these fees/costs

That's it. Then when you want to add them all up, you just add up all the records.

With that said, you need to check the spelling of all your field names in the calculation that isn't working. I bet you missed a vowel somewhere, or mistyped a word. Actually you have a stray + sign at the beginning of that final formula. That 's probably it.

 

[BlueIshDan]

You have a + sign before the leading Nz

 

[newbie4access]

MarkK, I appreciate your help. I have not learned VBA yet. All the VBA books on Amazon require previous knowledge, even the beginner books (which is where I need to begin). It seems nuts to buy a beginner VBA book that requires VBA knowledge. Do you know of any truly beginner resources?

plog, I may experiment with that over the weekend. But that may confuse me even more as any given item will have many fees associated to itself. How would you recommend input on a form; I currently have them listed and one just enters the data as known, right down the list. Then on another tab on that form I have the item totals running as each record is entered. Does that make sense?

 

[newbie4access]

BlueIshDan Hi, I removed it; I still get the same #Error/s.

 

[newbie4access]

Can anyone kindly share or demonstrate how to start coding this in VBA?

 

[plog]

With my structure you use a simple form/subform. The main form would be based on whatever table those values are currently in and the sub form would be based on the new table I proposed. Then users would have a drop down to select the fee/cost type and an input box to input the value of that fee/cost.

The added benefit is that whenever you need to add/remove a fee you don't have to redesign your report and all your calculations, you simply just add it to the drop down.

 

[newbie4access]

plog, I will try this Don't be surprised if I have more questions along the way Thanks

 

[MarkK]

Well, VBA and SQL. What I would do first is write a query that performs the subtotals, like I might first subtotal the Elim expenses, or do preliminary calculations, like . . .

-(Nz([AcqPricePd],0)*Nz([AcqQuan],0))

. . . in a query.
Then I would write a totals query that consumes the subtotals query so that I can move the solution ahead incrementally.
In the final step, I would handle the current event of the form, and open a recordset on your final summaries query,

private sub form_current()
   const SQL as string = _
      "SELECT * " & _
      "FROM qMyTotalsQuery " & _
      "WHERE OrderID = "
   dim rst as dao.recordset

   if not me.newrecord then 
      set rst = currentdb.openrecordset(sql & me.orderid)
      if not rst.eof then
         me.txtAcqPricePaid = !AcgPricePaid
         me.txtEtc = !Etc
         ...
      end if
      .close
   end if
end sub

. . . and thereby display the results as calculated for the current record.

See if that makes sense,

 

[newbie4access]

MarkK, I will try this too. Thank you so kindly for the head start.

 

[newbie4access]

Fee_Amount, number, amount of fee

plog, Hi again ... I am trying your setup with the fees. But I am confused (sorry). I see how this would work if the fees remained the same, but ... all of the fees will almost always be different, never set nor consistent.

For instance, a Listing Fee amount can be zero (with a special offer from a venue), a varying flat rate, a varying percentage rate or a combination amount (depending on the venue). Would this setup really work for this application?

Thank you.

ItemTable
ItemID (an/pk)
ItemTitle (text)
ItemFeeType (lookup wiz from FeesTable below - allowing multiple values because an item will have some if not all the fees if it has sold)
FeePrice ... how do I do this?
ItemRelisted (How many times was it relisted)

FeesTable
FeesID (an/pk)
ItemID (n/fk)
FeeType {list: Listing Fees (1st time only), Miscellaneous Fees, Packing Fees, Relist Fees (2nd time or more), Shipping Fees, Sold Fees}
FeePrice ... or how do I do this?


:banghead:

 

[newbie4access]

I'm going to try the subform way now

 

[plog]

If the FeePrice can be different, then you allow the user to enter its value. In the example structure you posted, you did it incorrectly. There would be no Fee information in the ItemTable.

All that information would be solely in FeesTable. The two are linked via the ItemID, so there is no reason to duplicate that information in the ItemTable.