CODEWITHSUNDEEP
vbams-access
WKI
WKI

Reputation: 215

DCount using Multiple Criteria

I am not sure why my code does not return the right number of records. I have:

counter = DCount("*", "tbl1", "[Check] = FALSE OR DateDiff('d', [CheckDate], 
Date()) > 365 And [Room] = '" & Forms!frmSelect.txbSelect.Value & "'")
checkIndicator.Caption = "(" & counter & ")" & " available!"

It seems that the part with [Check] = FALSE OR DateDiff('d', [CheckDate], Date()) > 365 works fine but the the entire code is not. Thanks in advance!

Upvotes: 0

Views: 961

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

Your current code will return a count of records in tbl1 for which:

  • [Check] = FALSE (for any value of [Room])

OR

  • DateDiff('d', [CheckDate], Date()) > 365 AND [Room] = Forms!frmSelect.txbSelect.Value

Depending on the desired result, I'm guessing that you require [Room] = Forms!frmSelect.txbSelect.Value to always be true, and so the logic should be enclosed with parentheses in the following way:

"([Check] = FALSE OR DateDiff('d', [CheckDate], Date()) > 365) And [Room] = '" & Forms!frmSelect.txbSelect.Value & "'"
 ^                                                           ^
 |                                                           |
 +----------------- Added parentheses here ------------------+

This is now returning the count of records in tbl1 for which:

  • [Room] = Forms!frmSelect.txbSelect.Value

AND

  • [Check] = FALSE OR DateDiff('d', [CheckDate], Date()) > 365

Upvotes: 2

Related Questions