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

Continuous Form DCount #Error On New Record

100+
P: 124
On a continuous form I have a DCount expression as the control source for one of my textbox controls. The expression is thus:

=DCount("DegReqDiscId","DegReqDisc","DegReqId = " & [txtDegReqId])

It works fine except that on the new record row it displays #Error. I know it's doing that because the txtDegReqId is (New) and will be so until a new record is entered. Is there a way that I can get it to show blank (or 0) for the new record row?
Apr 20 '10 #1

✓ answered by TheSmileyCoder

Hi, and welcome to Bytes

Yes there is, we just make sure to handle the Null case. Nz([Field],value) will return value if [Field] is null.

We could use that like:
Expand|Select|Wrap|Line Numbers
  1. =DCount("DegReqDiscId","DegReqDisc","DegReqId = " & nz([txtDegReqId],0))
so now, if txtDegReqID is null the function will return 0 (And assuming that there is no ID with the value 0, the dcount will now return 0 as well)

However we can make it a bit smarter. There is no need to take up query time to perform a query, if know the answer allready. Lets use an IIf statement.
Expand|Select|Wrap|Line Numbers
  1. =IIf(isNull([txtDegReqId]),0,DCount("DegReqDiscId","DegReqDisc","DegReqId = " & [txtDegReqId]))
Now the query will only be performed if there is an actual ID.

Share this Question
Share on Google+
2 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi, and welcome to Bytes

Yes there is, we just make sure to handle the Null case. Nz([Field],value) will return value if [Field] is null.

We could use that like:
Expand|Select|Wrap|Line Numbers
  1. =DCount("DegReqDiscId","DegReqDisc","DegReqId = " & nz([txtDegReqId],0))
so now, if txtDegReqID is null the function will return 0 (And assuming that there is no ID with the value 0, the dcount will now return 0 as well)

However we can make it a bit smarter. There is no need to take up query time to perform a query, if know the answer allready. Lets use an IIf statement.
Expand|Select|Wrap|Line Numbers
  1. =IIf(isNull([txtDegReqId]),0,DCount("DegReqDiscId","DegReqDisc","DegReqId = " & [txtDegReqId]))
Now the query will only be performed if there is an actual ID.
Apr 21 '10 #2

100+
P: 124
That's what I was doing wrong! I was putting the nz function around my entire =DCount expression instead of just the [txtDegReqId]. However, I like your use of the IIf function. I'd figured that the shorter your code is, the faster the performance, but apparently that's not always the case?

Thank you very much! Works like a charm!
Apr 21 '10 #3

Post your reply

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