DCount using multiple criteria using AND

[bd528]

Hi all,

I have a text box on my form, who's control source is

=DCount("Quote_ID","tblNotesNew",("Quote_ID =" & [QuoteID] And "Note_Type = 'Quote'"))

This seems to be counting to be counting all records from tblNotesNew where the Note_Type is Quote, and ignoring the Quote_ID part of the DCount.

How can I adjust this so the DCount factors in both criteria?

Thanks

 

[Ranman256]

help differentiate items on a form and fields in the table:

DCount("Quote_ID","tblNotesNew",("[Quote_ID] =" & me.QuoteID & " And [Note_Type]='Quote'"))

(you dont adjust dcount)

 

[JHB]

I don't know if it is a typo:

=DCount("Quote_ID","tblNotesNew",("Quote_ID =" & [QuoteID] And "Note_Type = 'Quote'"))

Try:

=DCount("Quote_ID","tblNotesNew","Quote_ID =" & [QuoteID] & " And Note_Type = 'Quote'")

 

[Minty]

You haven't constructed the criteria correctly. Try

=DCount("Quote_ID","tblNotesNew","[Quote_ID] = " & [QuoteID] [COLOR="Red"]& " [/COLOR]And [Note_Type] = 'Quote'")

For clarity I tend to always put the field names in criteria in square brackets, just to remind me what is being compared to what. More examples in the link in my signature.

 

[bd528]

Thanks all for your suggestions.

Minty & JHB, your suggestion for great, thank you.

Ranman256, I get a #Name error using your code. Not sure what I'm doing wrong.

Either way, problem solved. Thanks again.

 

[Minty]

The use of Me.ControlName references the control on the form, Ranman's would be the correct syntax if you were using the DCount in VBA attached to the form.