DCount woes in my quarterly reports

[firestorm52]

I have created a db that provides a snapshot report of various maintenance activities since the beginning of the program i.e. when the db was first created/installed. My sups have asked if it is possible to get the report broken down into quarterly segments though and I'm having trouble getting the DCount functions that I previously used to work correctly. The previous code looked like this:

=DCount("[P3]","qryP3","[Time Required] <= & 0.010416667")

The only thing that I've changed was to include a "Between [Beginning Date:] And [Ending Date:] filter to the Date field of qryP3 (which the report is based off of). Now, whenever I run the report and enter in a date segment I get "ERROR#". What did I miss?

 

[pbaldy]

For one thing, since you've hard coded a time value, you don't want the ampersand (&) in there.

 

[firestorm52]

Oops. I meant to paste this one instead . . .


=DCount("[P3]","qryP3","[Time Required] <= " & 0.010416667)

 

[pbaldy]

If "time required" is a date/time field, you'd have to use octothorpes to surround the value. This works:

=DCount("*","QueryName","[FieldName] < #6:00am#")

 

[firestorm52]

What I am looking for are all records that were completed within 15 minutes of being called in. Is your method faster or more preferable?

 

[pbaldy]

I assume that the "time required" field contains that value (the number of minutes for completion)? You could try:

=DCount("*","QueryName","[FieldName] < #00:15am#")

Or maybe you can post a sample db?

 

[firestorm52]

I'll work on getting a sample db together. I forgot to mention that [Time Required] is a variable that was setup in qryP3.

 

[firestorm52]

Here's a sample db . . .

 

[pbaldy]

I must be missing something; the formula appears to be working correctly. It returns 147 records.

 

[firestorm52]

Sorry for not including any explanations. The Sample DB is what the form currently looks like. Users should be prompted for a beginning and ending date to filter the data from qryP3. That's where thee #Error shows up. I've included a copy of the database with the code that I have tried to use.

 

[pbaldy]

I don't think domain aggregate functions like parameter queries like that. Here's a different way of doing it. I created a new form for inputting the dates, and changed the query to look at the form instead of asking for the parameters. All your formulas seem to be working now. Open frmCriteria, input dates, then open the report (leaving the form open).

 

[firestorm52]

Thanks for your advice pbaldy. I ended up creating another form with that asks for the beginning and ending dates (like you said) and everything worked out well. Now I'm attempting to use DCount on a filtered version of qryP3. Of the records that match my criteria i.e. within the date range AND check-marked, I want to know how many required less than 15 minutes to complete. As of right now, I can get the total number of records for the requested dates along with a count of how many of those records are check-marked, but I'm having trouble counting those that are check-marked, within the date range, AND completed within 15 minutes. Any suggestions?

 

[pbaldy]

Something like this, since your query is already doing the date range filtering. These may not be the correct fields, I just merged a couple of yours:

=DCount("[P3]","qryP3","[P3] = TRUE AND [Time Required] <= " & 0.010416667)

 

[firestorm52]

Once again thanks for your help. My report is finally complete!! On to the next project . . .