Weird DCount problem

[AlanS]

WIAEnrollmentsDetailQ1 is a select query, which contains a calculated String field named GrantGrp. The query works as intended as the RecordSource for several detail reports, and when opened directly from the user interface. It is also the RecordSource for a summary report, which is intended to provide a count of the number of records for each value of GrantGrp. In the summary report's GrantGrp footer, there is a control named txtGrantGrp (bound to GrantGrp) and another control named txtGroupCount (bound to the expression =DCount("sSSN","WIAEnrollmentsDetailQ1","GrantGrp = '" & [txtGrantGrp] & "'"). txtGroupCount always displays "#Error". When I use the same DCount function in a Sub (after first DIMming txtGrantGrp as String and assigning an appropriate value to it), it generates the error "Data type mismatch in criteria expression."

I've used DCount like this for years without any problem, and am totally stumped about what the problem is. Any help will be greatly appreciated.

 

[pdx_man]

Well, for starters you have a control named txtGrantGrp and then you are dimensioning a variable in VBA of the same name. Access will definately get confused. I'm suprised you didn't get some kind of Dr. Watson type of error and nuked out of your DB.

What is the data type for the GrantGrp field?

Confirm the Name of the control for GrantGrp is txtGrantGrp and try:

Me.txtGroupCount = DCount("sSSN","WIAEnrollmentsDetailQ1","GrantGrp = '" & Me.txtGrantGrp & "'")

in VBA code. Remove the DIM statement.

Well, that's a couple things to look at. Let me know how these work.

 

[AlanS]

I probably didn't explain this clearly enough. The last statement of my first paragraph was something I was trying instead of, not in addition to, the actions described in the rest of the paragraph. What I meant was, when the DCount function didn't work in the form, I tried to make it work in a Sub instead (just to test the syntax of the function call) and since there was then no form field named txtGrantGroup to refer to, I DIMmed a variable by that name as a stand-in. Doing that also had the advantage of generating an actual error message, rather than the cryptic "#Error".

The GrantGrp field in the query is of type String. In the query definition, that column is defined as: GrantGrp(iLeadGrant). GrantGrp is a Public String function defined in a standard module, and it works fine.

I eventually gave up on this and solved the problem by doing the group sums in the query itself rather than in the report based on the query, but I still am wondering why it didn't work as I originally had it. It just now occurs to me that the problem may be that references to the GrantGrp field in the query may have been confused with the GrantGrp function.

 

[pdx_man]

Confusing Access can be very easy when it comes to the scope of functions and variables and fields. I would say that is exactly the problem in this case. It probably didn't know which item you were trying to reference.