Dcount function gives #ERROR when used with parameter query

[theSizz]

I have a report that uses the DCount function as such: =DCount("[Midd]","qry3ptMiddles","[Midd]='Middle'&quotIt works as expected if the criteria in the query's date field is typed into the query. However, if I change the query's date field criteria to use a parameter query that's to be input by the user,I get an #ERROR result in the DCount formula when I run the report. I know the syntax for the parameter query is correct, because the query gives the expected results. Why won't the DCount formulas work when the parameter query is used? Any help would be appreciated.

 

[JeremyNYC]

I don't know the answer, but I wouldn't be surprised if the DCount function (and those related) simply won't work with parameter queries.

In general I avoid using DCount and it's ilk, as they're quite slow. Although it takes more programmer effort, it's much more efficient for the user if you replace the DCount with a home-grown function that returns the value you want.

It might make sense to make a form that gathers the parameter you are looking for and only opent eh report from this form. Then in the function that replaces the DCount (which would use a recordset to gather the data) you could refer to the control on the form where the user selected (or input) the parameter.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[LittleSmudge]

DCount function (and those related) DO work with parameter queries - but getting the double and single quotes in EXACTLY the right places can sometimes be a real headacke.

Post what you tried to use and I'll check it over for quote location.



G LS

 

[theSizz]

Hi little smudge. Thanks for the reply. Here's the the code that's in the control source of a text box in the report footer section.
=DCount("[Midd]","qry3ptMiddles","[Midd]='Middle'&quot
[Midd] is a calculated expression that is in the query qry3ptMiddles.
Here's the expression that's in [Midd]
Midd: IIf([team points]+[team line]-[opp points]=0,"middle","&quot
The query and report both run properly when I manually insert criteria in the date field of the query such as >#8/1/02#. However, when I replace the criteria in the date field with the parameter query >[Enter Starting Date]the query runs properly but the report produces #ERROR when it tries to evaluate the DCount function.
Thanks for your interest any help would be appreciated.

 

[LittleSmudge]

I still need more info.

I'd like to see the SQL string for qry3ptMiddles

but I suspect that this is a timing issue.

Is the DCount the only thing that relies on qry3ptMiddles ?







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[theSizz]

Little Smudge here's the SQL string for the query.

SELECT [Team Data].Date, [Team Data].[Week#], [Team Data].SeasonYear, [Team Data].Team, [Team Data].[Team Line], [Team Data].Opponent, [Team Data].Home, [Team Data].[Team Points], [Team Data].[Opp Points], IIf([Team Points]>[Opp Points],[Team Points]-[Opp Points],[Opp Points]-[Team Points]) AS Spread, IIf([team points]+[team line]-[opp points]=0,"middle","&quot AS Midd, IIf([team points]+[team line]-[opp points]=1,"side",IIf([team points]+[team line]-[opp points]=-1,"SIDE","&quot) AS Side
FROM [Team Data]
WHERE ((([Team Data].Date)>[Enter Starting Date]) AND (([Team Data].[Team Line])=3) AND (([Team Data].Home)="y&quot);

The above produces the error in the DCount function.

This next one that changes the WHERE statement runs fine.

SELECT [Team Data].Date, [Team Data].[Week#], [Team Data].SeasonYear, [Team Data].Team, [Team Data].[Team Line], [Team Data].Opponent, [Team Data].Home, [Team Data].[Team Points], [Team Data].[Opp Points], IIf([Team Points]>[Opp Points],[Team Points]-[Opp Points],[Opp Points]-[Team Points]) AS Spread, IIf([team points]+[team line]-[opp points]=0,"middle","&quot AS Midd, IIf([team points]+[team line]-[opp points]=1,"side",IIf([team points]+[team line]-[opp points]=-1,"SIDE","&quot) AS Side
FROM [Team Data]
WHERE ((([Team Data].Date)>#8/1/2002#) AND (([Team Data].[Team Line])=3) AND (([Team Data].Home)="y&quot);
You'll notice that everything is the same except the parameter query WHERE ((([Team Data].Date)>[Enter Starting Date]) is replaced with the date >#8/1/2002# being entered directly in the query.

 

[BSman]

Drive the report from a form.

Set a control on the form where the user enters the date. Let's call it txtLowestDate.

In your query, right click in the query box and build the criteria using the Builder so that the result is that the date is > txtLowestDate. (I've found that if even if you type in the correct syntax, Access may not recognize it as correct, but if you use the criteria builder, it works.)

Your DCount function will then use the txtLowestDate control as it's source for the date. Just be sure to write the DCount function (if you call the report from the form) as (I'm assuming that Middle is the date):

DCount("[Midd]","qry3ptMiddles","[Midd]=#" & me.txtLowestDate & "#&quot

Note that the date value must be surrounded by # sign for Access to use it properly.

 

[LittleSmudge]

And WE can all now see the problem

In getting rid of the hard coded date - you have also got rid of the date delimitor flags that tell JET that the data is to be a DATE.

You need to reinstate the # # characters.

They are on the keyboard for a reason, they only have one use in life, so come on use them .


'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.