Should Dcount return 0 for no records?

[PeterWieland]

It seems strange to me, but I am finding that Dcount returns a 'Invalid use of null' error when there are zero records. Surely it should return 0.

I can make this work by using an if and trapping the error, but it isn't very tidy.

 

[Rich]

Use the Nz function

 

[PeterWieland]

I tried that, and it still returns an error!

Nz(DCount([ID], "qryReturnUsersRecords", "[LessonNo] = " & Forms!frmGetInputData!txtLessonNo), 0)

 

[Rich]

Try
DCount(Nz([ID],0), "qryReturnUsersRecords", "[LessonNo] = " & Forms!frmGetInputData!txtLessonNo)

 

[PeterWieland]

Yep, I'd already tried it like that as well!

 

[Bat17]

DCount always returns 0 for me! Is there anything else that could be affecting it?

Peter

 

[Bat17]

another possibility could be

DCount([ID], "qryReturnUsersRecords", "[LessonNo] = " & NZ(Forms!frmGetInputData!txtLessonNo,0))

Peter

 

[PeterWieland]

I am at home now, so I will try this tomorrow.

I don't know if it makes a difference, but the query is an SQL server 'view'. This is the only thing I can think of that might be affecting it.

 

[PeterWieland]

I've tried the last suggestion, and I am still getting 'Invalid Use Of Null'. I have tried this line as the ControlSource property of a text box, as well as in the code of a command button, and still no joy. Could it be something to do with using SQL server as the back end?

 

[The_Doc_Man]

DCount("[ID]", "qryReturnUsersRecords", "[LessonNo] = " & NZ(Forms!frmGetInputData!txtLessonNo,"0"))

Unless [ID] is actually a string that holds a field name itself. But from context, I'm not reading that possibility. DCount builds an SQL string which it executes via something like DoCmd.ExecuteSQL. Then it reads the result and passes that back to you. That is why you need to assure that the arguments are consistent with that goal - SQL generation.

Watch:

DCount("[ID]", "qryReturnUsersRecords", "[LessonNo] = " & NZ(Forms!frmGetInputData!txtLessonNo,"0"))

Parse that out to recognize
Function = DCount
Arg1 = "[ID]"
Arg2 = "qryReturnUsersRecords"
Arg3 = "[LessonNo] = " & NZ(Forms!frmGetInputData!txtLessonNo,"0")

Now take template "SELECT COUNT(" & arg1 & ") FROM (" & arg2 & ") WHERE " & arg3 & ";" - which is because it is DCount.

Do the subsititution, execute it, and trap the result, which will be a single record in a recordset with one field. See how it works?

Oh, make sure that Forms!frmGetInputData!txtLessonNo is text format, too. The substitution won't attempt an automatic CStr() call to convert it.

 

[PeterWieland]

Thanks DocMan, the quote around [ID] did it!

That was a brilliant explanation, I finally understand how the syntax of the Dcount function works. Can I presume that the same theory applies to the othe domain functions?