By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

#error using Dsum

100+
P: 222
Hello

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

Expand|Select|Wrap|Line Numbers
  1. =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
Aug 12 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. =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
Aug 12 '08 #2

100+
P: 222
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:

Expand|Select|Wrap|Line Numbers
  1. =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
Aug 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.