DCount Function

[jzacharias]

I can't figure out why this DCount returns #Error...
=DCount("Citation_ID","qryMonthlyTickets","[Offense] = 'Speeding'")

I placed it in the control source of a textbox.

 

[Isskint]

does the query return good data when run manually?

 

[John Big Booty]

Access displays #Error in a control when it cannot evaluate the expression that supplies the control's value. You use the ControlSource property to supply the source of the control's value. For example, if you supply an expression for the ControlSource that includes a function, and there are too few arguments supplied to the function, or if the arguments you supply are incorrect, Access displays #Error in the control.

What sort of value does [Offense] hold?

 

[jzacharias]

It holds the offense names like speeding, DUI, no insurance

 

[boblarson]

Does the field Citation_ID have the underscore in the actual name?

Any lookup fields directly in the table?

 

[jzacharias]

Yes... Should I not do that?

 

[jzacharias]

That is the primary key

 

[boblarson]

Yes... Should I not do that?

NOPE, see here for why:
http://www.mvps.org/access/lookupfields.htm

 

[jzacharias]

I did the lookup on the form and not the table

 

[jzacharias]

The yes was for the underscore..I did not use any lookup tables at the table level

 

[boblarson]

The yes was for the underscore..I did not use any lookup tables at the table level

Ah, communication misunderstanding.

Underscores are fine but sometimes people think that they can substitute them for spaces like Access does in certain circumstances and have it still refer to the same thing. I, personally, use CamelCase unless I absolutely need underscores.

 

[boblarson]

What is the name of the text box which gets the #Error?

 

[jzacharias]

OffenseCount

 

[boblarson]

And the field name OFFENSE is in that query (qryMonthlyTickets)? What is the SQL of that query?

 

[jzacharias]

SELECT tblCitation.Citation_ID, tblShift.Shift, tblRotation.Rotation, tblDistrict_Contract.District_Contract, tblOffenses.OffenseType, tblOffenses.Offense, tblCitation.CitationDate, [tblDeputy].[Last] & ", " & [tblDeputy].[First] & " " & [tblDeputy].[Middle] AS Expr1, tblDeputy.Last, tblDeputy.First, tblDeputy.Middle
FROM ((tblShift INNER JOIN ((tblCitation INNER JOIN tblDistrict_Contract ON tblCitation.District_ID = tblDistrict_Contract.District_ID) INNER JOIN tblDeputy ON tblCitation.Deputy_ID = tblDeputy.Deputy_ID) ON tblShift.Shift_ID = tblDeputy.Shift_ID) INNER JOIN tblOffenses ON tblCitation.Offenses_ID = tblOffenses.Offenses_ID) INNER JOIN tblRotation ON tblDeputy.Rotation_ID = tblRotation.Rotation_ID
WHERE (((tblCitation.CitationDate) Between [Start Date] And [End Date]));

 

[boblarson]

Aha!! I think I spot the problem.

See if it works if you take the WHERE CLAUSE out. Having parameter prompts will cause an error because the DCount doesn't run the query in the conventional sense. So it can't get the values it is looking for. An input form which can remain open and available to the query is the way you really should go. Then the DCount should work.