From: Jen on 29 May 2010 18:42 Using Access 2003 SP3 - in Forms I know I'm probably asking a very complex question, so a very big THANK YOU to whomever can help. I am doing a detailed inventory and need help with an expression in my form that will calculate my profit based on one of three fields. Only one field would have the end data. To clarify what I am working with: Most stock items come by the "unit" and contain multiple "subunits" which, in turn, contain smaller "pieces." However, some "units" only come with "subunits" and some only come as "units." Here is what I have done so far, along with the (format) and followed by the expression. Unit Cost (currency) ~ CS:Unit Cost Units on hand (number) ~ CS:Units on hand Cost of all Units on hand (currency) ~ =Nz([UnitCost],0)*Nz([UnitsOnHand],0) Number of Subunits per Unit (number) ~ CS:Number of Subunits per Unit Subunit Cost (currency) ~ =IIf([NumbSubUnitsPerUnit]=0,Null,Nz([UnitCost])/Nz([NumbSubUnitsPerUnit])) Subunits on hand (number) ~ CS:Subunits on hand Cost of all Subunits on hand (currency) ~ =Nz([SubUnitCost])*Nz([SubUnitsOnHand]) Number of Pieces per Subunit (number) ~ CS:Number of Pieces per Subunit Piece Cost (currency) ~ =IIf([NumbPiecesPerSubUnit]=0,Null,Nz([SubUnitCost])/Nz([NumbPiecesPerSubUnit])) Pieces on hand (number) ~ CS:Pieces on hand Cost of all Pieces on hand(currency) ~ =Nz([PieceCost])*Nz([PiecesOnHand]) Total Cost of All on Hand (currency) ~ =Nz([CostUnitsOnHand])+Nz([CostSubUnitsOnHand])+Nz([CostPiecesOnHand]) Selling At (currency) ~ CS:Sell At Profit (currency) ~ =[SellingAt]-[??cost on hand??]** **Here is where I need the help. I need an expression that will calculate the profit based on whichever of the following fields contain the final "cost on hand" data: Cost of all Units on hand , Cost of all Subunits on hand , or Cost of all Pieces on hand Typically the final cost on hand data will be in "Cost of all Pieces on hand", but occasionally it will be in one of the other two. Is there an expression that I can use that will find the last of the three calculated data fields and use that data? If there isn't, how would I go about creating a combo box list that would include only the calculated data in those fields for only the current record? And if this is possible, could I then use the value displayed in the combo box to figure the profit? Again, Thanks ahead of time. J From: Lord Kelvan on 30 May 2010 17:27 mmm well my guess would be [cost on hand]=iif(Nz([PieceCost])*Nz([PiecesOnHand])<>0, Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([PieceCost])*Nz([PiecesOnHand])<>0, Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand], 0))) This checks if the cost of pieces is 0 if not then it says the cost on hand is the cost of pieces if it is 0 then it checks the subunits if the sub units cost is not 0 it will use the subunits cost as cost on hand and if that is 0 it will use the units as cost on hand. Though this code wont work if you have pieces lets say and they have no cost to the company then I would use this code [cost on hand]=iif(Nz([Pieces on hand])<>0, Nz([PieceCost])*Nz([PiecesOnHand]),iif(Nz([Subunits on hand])<>0, Nz([PieceCost])*Nz([PiecesOnHand]),Nz([UnitCost],0)*Nz([UnitsOnHand], 0))) This will do the same but will check to see if the pieces on hand value is 0 and so on rather than the cost value Hope this helps Regards Kelvan  |  Pages: 1 Prev: Employee Barcode scan IN and OUT using Access 2007Next: Password protection