Continuous Form DCount #Error On New Record

[bullfrog83]

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

=DCount("DegReq DiscId","DegReq Disc","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?

[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:

=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.

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

Now the query will only be performed if there is an actual ID.

[bullfrog83]

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!