#error using Dsum

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • wassimdaccache
    New Member
    • Apr 2007
    • 222

    #1

    #error using Dsum

    Hello

    I have a subform base on query. I used a textbox with a control source =

    Code:
    =nz(DCount("ref_ID","checklist","[profession_id]=" & [ProfessionId] & "and" & "[passed]= " & True & " and" & "[blockid]='" & [Forms]![block_id]![block_id] & "'  "),0)
    Each time we add a new record to the subform it works properly unless the last record (I mean the newrecord that it doesn't containt any values)It is #ERROR.
    Actually for me it is not a problem but for my boss it is :S.

    He asked me to change it to 0 , "" or anything else then #error.

    Any Idea how to do that ? NZ function is not working


    CHEERS;

    WASSIM S DACCACHE
  • Stewart Ross
    Recognized Expert Moderator Specialist
    • Feb 2008
    • 2545

    #2
    Hi Wassim. The null values that cause the error relate to the parameters passed to the DCount function itself, not to the result of the DCount, so Nz applied to the DCount result does not help.

    I suggest you use an IIF to test one of the underlying field values and if the field is null return a 0 in place of the DCount:

    Code:
    =IIF(IsNull([ProfessionID]), 0, DCount("ref_ID","checklist","[profession_id] = " & [ProfessionId] & " AND [passed] = " & True & " AND [blockid] = '" & [Forms]![block_id]![block_id] & "'"))
    I have adjusted the spacing of the arguments slightly - you were leaving out spaces between some operators and concatenating others without really needing to.

    -Stewart

    Comment

    • wassimdaccache
      New Member
      • Apr 2007
      • 222

      #3
      Originally posted by Stewart Ross Inverness
      Hi Wassim. The null values that cause the error relate to the parameters passed to the DCount function itself, not to the result of the DCount, so Nz applied to the DCount result does not help.

      I suggest you use an IIF to test one of the underlying field values and if the field is null return a 0 in place of the DCount:

      Code:
      =IIF(IsNull([ProfessionID]), 0, DCount("ref_ID","checklist","[profession_id] = " & [ProfessionId] & " AND [passed] = " & True & " AND [blockid] = '" & [Forms]![block_id]![block_id] & "'"))
      I have adjusted the spacing of the arguments slightly - you were leaving out spaces between some operators and concatenating others without really needing to.

      -Stewart
      I appreciate your Kind help for me. It is working properly with the criteria optimized

      Best regards,

      WASSIM S DACCACHE

      Comment

      Working...