Results 1 to 6 of 6
  1. #1
    postnaarmij is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    2

    Question IIF Function Doesn't work in a control souce expression.

    Dear Reader

    I have a little trouble with the Function IIF in an expression.

    I have a sub report with a row count,


    The total count is displayed in a text control on the main report,
    so far so good,
    But if there are no records shown in the sub report, the form is’nt displayed and the control field in the main report shows a #error,

    So i tried to solve it be creating another text control field in the main report and give this control field the following expressing in the control source:
    Code:
    =IIf(OnError([aantal_row_opnaam]);0;[aantal_row_opnaam])

    What I simple want to happen is,
    If there is an error in the control field [aantal_row_opnaam] than display 0, and if there is no error in the [aantal_row_opnaam] display the value from that control field.

    But what error or value this text field has, the control field with the iif function is not displaying anything.
    There is something that i’m simply missing, but I can’t find what that is.

    Can anyone see what i'm missing??

    Thanks in advance for your time.

    Kind regards,
    Patrick
    Last edited by postnaarmij; 01-20-2018 at 11:54 AM.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Unless the settings are different in your location, each part of the IIf statement should be separated by a comma
    However I would use Nz rather than OnError
    Try this instead

    Code:
    = Nz([aantal_row_opnaam],0)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't think onerror is a valid function in vba

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    It should be IsError - but no need to use it at all...
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    postnaarmij is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    2
    Dear Ridders52 and Ajax,

    Your right that the separte symbol is normale a comma,
    But apparently on the system where i'm working on its a colon,
    Thank you so much for replying,
    The NZ function doesn't work for my, because i get a #groot (#great) error in the control field.
    The following code works great,
    Code:
    =iif(Iserror([controlfield]);0;[controlfield])
    for my Curiosity, What are the difference between the Onerror and Iserror functions.

    Thank you both for your quick reply and help.

    Kind Reagards

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    for my Curiosity, What are the difference between the Onerror and Iserror functions.
    The simple answer is that the IsError function exists and OnError doesn't
    You may be getting confused with error handling in a procedure.
    For example:

    Code:
    Sub ClearData()
    
    On Error GoTo Err_Handler
    
    
        Dim intC As Integer
        For intC = 1 To 30
                Me("L" & intC).Caption = ""
                Me("Bar" & intC) = ""
                Me("L" & intC).visible = False
                Me("Bar" & intC).visible = False
        Next intC
    
    
    Exit_Handler:
        Exit Sub
    
    
    Err_Handler:
        MsgBox "Error " & Err.Number & " in ClearData procedure: " & Err.Description
        Resume Exit_Handler
        
    End Sub
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 12
    Last Post: 05-02-2017, 07:39 PM
  2. Replies: 3
    Last Post: 09-19-2014, 07:58 AM
  3. Replies: 3
    Last Post: 01-07-2014, 09:57 AM
  4. Replies: 2
    Last Post: 01-03-2011, 05:17 PM
  5. Active X - Web Browser control doesn't work
    By forstatd in forum Reports
    Replies: 1
    Last Post: 06-02-2010, 10:56 PM

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