DCount function with Combobox Criteria

[GreenJay]

I am working with MS Access 2003

I have a form (frmCalendar) with a textbox (tbDay) and a combobox (cmbLineName)

I want to count the number of records based on the criteria from cmbLineName, and show the result in tbDate. Inside the Control Source of tbDate I put in the following:

=DCount("[EncounterID]","tblEncounters","[LineName] = ' " & "Forms![frmCalendar]![cmbLineName] = " ' ")

The problem is that the result in tbDate is always 0; regardless of what is chosen in cmbLineName. I know that the record count should not be 0 for all criteria.

My question, I'm affraid, is general in nature: Why does the code above not work the way I had inteded?

Thank you for taking the time to read this.

 

[MarkK]

This . . .

"[LineName] = ' " & "Forms![frmCalendar]![cmbLineName] = " ' "

. . . doesn't evaluate how you think. Forms![frmCalendar]![cmbLineName] is a reference to a value in an object on a form, but when you enclose it in quotes . . .

"Forms![frmCalendar]![cmbLineName] = "

. . . it is just text. You need . . .

"[LineName] = '" & Forms![frmCalendar]![cmbLineName] & "'"

 

[GreenJay]

Thanks for your help MarkK. It works great now!

 

[Galaxiom]

The main problem is the small mistake with the equals operator instead of the concatenator before the close quote.

Access evaluates references in the argument string. It handles the datatype automatically so you can forget about the single quotes and the concatenators.


This will work:

=DCount("[EncounterID]","tblEncounters","[LineName] = Forms![frmCalendar]![cmbLineName]")

Moreover if the combo is on the same form as the control then the reference to the combo will default to the current form without going through the Forms collection.

=DCount("[EncounterID]","tblEncounters","[LineName] = [cmbLineName]")

Concatenation is really only required when building strings that are fed directly to the database engine (eg CurrentDb.Execute).

If the reference is in the Application then Access will handle it before sending to the engine. This is why DoCmd.RunSQL can handle references to objects on a form but Execute cannot.

 

[GreenJay]

Thanks for all the great info. It works wonderfully now!!