How to Display Total Amount of Records past a certain Time

[trevor2524]

Hello,

I have a query running data that pulls all the Dates and Time for a particular day. The Field is called Completion Time and it is a Date/Time column. I want to on a Form display in a textbox the amount of records that are past 2:30 pm. What do i need to put in the textbox field in order to make this work? Thanks for the help.

 

[plog]

You would use a DCount (http://www.techonthenet.com/access/functions/domain/dcount.php). In the criteria part of the Dcount you would compare the TimeValue (http://www.techonthenet.com/access/functions/date/timevalue.php) of your field to 2:30 pm.

 

[trevor2524]

I tried that but unfortunately i'm getting an error.

=DCount(" [Testing]![Completion Time]","[Testing]","[2:30:00 PM]")

and would this count all records after 2:30?

 

[plog]

Read the Dcount documentation link.

The 1st parameter is just the field name, not the table then the field name.

The 2nd parameter is the table. That looks good--actually I'd remove the brackets.

The 3rd parameter is comparison criteria. You don't have any comparison there, just a time. You need to use the TimeValue function on your field and then compare it to 2:30.

 

[trevor2524]

I'm included the table becaue the form is not bound to that table its bound to another table. I changed the textbox code to the following:

=DCount("[Testing]![Completion Time]"," [Testing]",TimeValue("2:30:00PM")<[Testing]![Completion Time])

and I'm getting a Name? error in the textbox

 

[plog]

That means it can't recognize a field you are referencing. Despite your reasoning you do not need to include the table in the first parameters (nor the third). The second parameters dictates what table is used. Again, I'd remove the brackets from the table.

Also, each parameters needs to be a string--which means enclosed with quotes. You've done that with the 1st and 2nd parameters, but your third parameter isn't.

 

[trevor2524]

I did everything you suggested and the inside the textbox I'm getting an #error messge

 

[plog]

Try removing the parameter portion. Just supply the field and table names and get it to work. THen try adding the criteria.

Can you post what you have?

 

[trevor2524]

I have this i removed the criteria but I'm still getting the #error message

=DCount("[Completion Time]"," [Testing]")

Does it effect it if the Testing paramater is actually a query?

 

[plog]

No, but the brackets might. Remove the brackets around Testing and get rid of the space preceding it inside the quote marks.

 

[trevor2524]

Doing that brought back the #name error

 

[plog]

Does the Testing table/query in fact have a field called "Completion Time"?

Can you post your database?

 

[trevor2524]

Yes there is. It shows up when I use the build function. Unfortunately. i cant post the database.

 

[plog]

Can you post a screen shot of the DCount code and of the Testing query in design view?

 

[Galaxiom]

I would derive a field for just the TimeValue of [Completion Time] in the form's RecordSource query.
Let's call the field [CompletionTimeOnly].

Enter the following in the Field cell of a column in the query designer

CompletionTimeOnly: TimeValue([Completion Time])

Then in a footer or header textbox of the form

=Sum(IIF([CompletionTimeOnly]>#2:30:00 PM#,1,0))

This uses the data in the form rather than a separate DCount back to the table.