I would probably do this calculation in the form query and not in a control.
Is the form name "Data Entry"? (You shouldn't have spaces in object names).
If Yes, you don't need the collection and form name: Instead of
Code:
IsError([Forms]![Data Entry]![BagsProduced]/(DLookUp("[StandardBPM]","[tblStandardBPM]",_"[BagSizeID]=" & [Forms]![Data Entry]![BagSizeID] & " and [LineNumberID] = [Forms]![Data Entry]![LineID]")*(480-[ExemptDT])))
you can use
Code:
IsError([BagsProduced]/(DLookUp("[StandardBPM]","[tblStandardBPM]",_"[BagSizeID]=" & [BagSizeID] & " and [LineNumberID] = " & [LineID] & ")*(480-[ExemptDT])))
You have a value (calculation/formula) for the TRUE part, but it looks like the FALSE part of the IIF() function is missing.
The syntax of the IIF() function is
IIf(expr, truepart, falsepart)
The expr is the ISERROR() function. (the function ISERROR() will return a TRUE or FALSE)
The TRUEpart is the calculation/formula (the same as what is in the ISERROR() function)
The FALSEpart is missing.
You could be getting a divide by zero error
You could be getting the error because the controls are NULL. In this case, you could use the NZ() function for the form controls.. ie NZ(BagsProduced,0)
I'm going to guess that the controls are on the form "Data Entry".
So building up the calculation:
Code:
=IIF(expr, TRUE, FALSE) <<- syntax
=IIF(ISNULL(),TRUE, FALSE) <<- substitute "expr"
=IIF(ISNULL(),TRUE, 0) <<- substitute zero for the FALSEpart
=IIF(ISNULL(),[BagsProduced]/(DLookUp("[StandardBPM]","[tblStandardBPM], [BagSizeID]=" & [BagSizeID] & " and [LineNumberID] = " & [LineID] & ")*(480-[ExemptDT]), 0) <<- <<- substitute the calculation for the TRUEpart
=IIF(ISNULL([BagsProduced]/(DLookUp("[StandardBPM]","[tblStandardBPM], [BagSizeID]=" & [BagSizeID] & " and [LineNumberID] = " & [LineID] & ")*(480-[ExemptDT])),[BagsProduced]/(DLookUp("[StandardBPM]","[tblStandardBPM], [BagSizeID]=" & [BagSizeID] & " and [LineNumberID] = " & [LineID] & ")*(480-[ExemptDT]), 0) <<- add the calculation for the ISERROR() funnction.
You could also try:
Code:
=NZ([BagsProduced],0)/(DLookUp("[StandardBPM]","[tblStandardBPM], [BagSizeID]=" & NZ([BagSizeID],0) & " and [LineNumberID] = " & NZ([LineID],0) & ")*(480-[ExemptDT])
My $0.02........