DCount with two criteria

[Khalil Handal]

Hi to all,

I have this line of VBA code and is working well.

Me.txtLoanCount = DCount("*", "tblBorrowedBooks", "[fkBookId] = " & [pkBookId])

I want to add a second criteria for a date field to be null (DateHoldRequest Is Null) using AND operator.
I am not able to do it. Any suggestions please?

I tried this code

DCount("*", "tblBorrowedBooks", "[fkBookId]='" & [pkBookId] & "' AND [DateHoldRequest] is null")

But had an error: 3464 data type mismatch in criteria expression
Output: 2 AND [DateHoldRequest] is null)

Any ideas?
Khalil

[Welshgasman]

You went and put single quotes around a numeric value, when it did not need it in the first place, as shown by your previous working code.
Single quotes are for strings.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct. You might want to start doing that as well.

DCount("*", "tblBorrowedBooks", "[fkBookId]= & [pkBookId] & " AND [DateHoldRequest] is Null")

[Khalil Handal]

Hi,
Thank you for the reply and for information given.
In your code, the criteria shows only THREE double quotes; Should it be Four ?
It shows the line in red color in the VBA window.

[Welshgasman]

Yes, put one after the = sign.

[Khalil Handal]

Thank you very much.
Solved