DCOUNT with criteria

[jpkeller55]

I have a report called LeadTimeRpt based on a query called ShipDifferencAll. The query calculates the difference between two dates. I would like to print on the report the count of all the date differences that are 0 or 1 (ie <2). I am using the following statement but get ERROR as a value

=DCount([PrintDiffDays],"LeadTimeRpt",[PrintDiffDays]<2)

I appreciate any help getting this solved.

Thanks, JPK

 

[BoxHead]

Try

=DCount("[PrintDiffDays]","ShipDifferencAll","[PrintDiffDays]< " & 2)


Dcount looks at domains (tables and queries) not report values.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[jpkeller55]

That works for me as long as I don't use any parameter criteria in the query. Is there a way to get the DCOUNT function to work based on the query when the user is prompted for a date range?

Thanks!

 

[BoxHead]

I'm not understanding why it wouldn't work if there's a parameter in the query. DCount should count the results of the query. It could be that as an unbound control on the report, it is populated before the query result-set is built.

Try YourTextBox.Requery on the Detail Format Event.


Let me know if that works or not.

John

Use what you have,
Learn what you can,
Create what you need.

 

[jpkeller55]

I am not sure what you mean...I just know that when I put the Between/and statement as criteria in the query, the report returns ERROR. Without the parameter it works, but I need it to work with the parameter.

Thanks, Jim

 

[BoxHead]

Jim,

Where does "the report return ERROR"? Is it in the text box that has the DCount statement only or is it another issue?

If it's just the textbox with the DLookup, try changing it to

=DCount("*","ShipDifferencAll","[PrintDiffDays]< " & 2)

And if you have a textbox on the report that is Named PrintDiffDays, change it to txtPrintDiffDays. Don't change the control source. Just the name.




John

Use what you have,
Learn what you can,
Create what you need.

 

[jpkeller55]

John,

It is in the text box on the report. The value returns as ERROR. From what I can tell thru some of the other posts, it looks like DCOUNT doesn't work with a parameter query. I guess I will have to figure something else out.

Thanks, Jim

 

[BoxHead]

Live and learn. Sorry for that.

I used something like the following as the ControlSOurce in an unbound textbox on a report based on a parameter query and it gave me the correct results.

=Sum(IIf([PrintDiffDays]< 2,1,0))

Let me know if it works.



John

Use what you have,
Learn what you can,
Create what you need.

 

[jpkeller55]

John,

Here is a solution that I tried and now works. Instead of putting a parameter in the criteria for the ShipDate on the query, I am using a date range form. In the criteria of the query I place the following command (which is specific to my date range form)

Between [Forms]![frmDateSelect]![txtStartDate] And [Forms]![frmDateSelect]![txtEndDate]

On Open of the report, is coded to open the date range form.

Thanks for your help...couldn't have done it without you!

Thanks, Jim