Form Function Problem

[bcarter17]

Hello all,
I am working on an Access 2007 form and I have a need to create a calculated field, that will display the number of trucks on a job that have been actively picking up loads within the last 2 hours. Here is my setup.



The data source is a query named qryMichelsDeliveryOrder
The field I want to count with criteria is [Contractor]
The criteria would be using a field named [Last Loaded], then using current time, count the Contractor criteria only if the [Last Loaded] field is within the last 2 hours.

I am trying to using DCount as a function in the form field using the code below.

=DCount('[Contractor]','qryMichelsDeliveryOrder","[Last Loaded]>=DateAdd("h",-2,Now())")

However, I am getting a string error each time I try to insert it. Any thoughts on what I am missing?

[Micron]

Could be your mix of single and double quotes. Could also be that [last loaded] contains the date, in which case you would not be able to compare dates vs time. Could also be that you're trying to compare an hour value to a time value (2 vs 5:00 PM) but using Now will cause your expression to include the date. Seeing some data might help if modifying the quote marks doesn't fix it. I also think you'll need date delimiters. Try

=DCount("[Contractor]","qryMichelsDeliveryOrder","[Last Loaded]>=#" & DateAdd("h",-2,Now()) & "#")

Knowing the text of the error message is always helpful. The error number alone is not.

[jojowhite]

you may also try:

=DCount("[Contractor]",'"ryMichelsDeliveryOrder","Hour([Last Loaded])>=Hour(Now())-2")

[June7]

jojo, showing a stray solo apostrophe in that DCount expression. Expression probably needs to consider year and month in order to limit to "last 2 hours" as Madpiet shows, although concatenation and # should not be necessary.

Reverse apostrophes and quotes. This works for me:

DCount("*","qryMichelsDeliveryOrder","[Last Loaded]>=DateAdd('h',-2,Now())")