Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2019
    Posts
    20

    #Error in calculated control despite error handling

    I have the following expression in a calculated control on a form. Despite the error handling, an #Error message still appears in the control when I navigate to a blank record. The error goes away once I enter values into the input fields, but how can I prevent the error from appearing in the first place?



    Code:
    =IIf(IsError([Forms]![Data Entry]![BagsProduced]/(DLookUp("[StandardBPM]","[tblStandardBPM]",_
    "[BagSizeID]=" & [Forms]![Data Entry]![BagSizeID] & " and [LineNumberID] = [Forms]![Data Entry]![LineID]")*(480-[ExemptDT]))),"",_
    [Forms]![Data Entry]![BagsProduced]/(DLookUp("[StandardBPM]","[tblStandardBPM]",_
    "[BagSizeID]=" & [Forms]![Data Entry]![BagSizeID] & " and [LineNumberID] = [Forms]![Data Entry]![LineID]")*(480-[ExemptDT])))
    Thanks!

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, perhaps move that calculation to the On_Current event of the Form? It might require a refresh after you update the record but you shouldn't see #Error when going to a new record.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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........

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-06-2015, 12:06 PM
  2. Error handling of table update error?
    By panoss in forum Forms
    Replies: 5
    Last Post: 10-31-2014, 02:06 PM
  3. Error Handling Question - On Error Goto
    By redbull in forum Programming
    Replies: 7
    Last Post: 12-06-2012, 07:54 AM
  4. Replies: 3
    Last Post: 09-05-2012, 10:23 AM
  5. Error 2501 displays with Error handling
    By agent- in forum Programming
    Replies: 13
    Last Post: 08-05-2011, 02:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums