Query records more than 30 minutes old

[skwilliams]

I have a table with records that I would like to run a append query which appends records to another table and deletes from this table if the status = "Complete" and the CmpltTime is >= 30 minutes. The CmpltTime is a default value based on the system time that is autofilled.

How can I set the criteria for >= 30 minutes?

 

[pbaldy]

You can add a field to the query using DateDiff() to calculate the minute difference, and put a criteria on that field. Archiving is typically not a good idea, and in this case you have the possibility that a record will become 30 minutes old between the append and the delete.

 

[skwilliams]

I tried using this query on the table without the append, just to see if the equation would work for me. I created a field in the query. This is the calculation.

Cur: DateDiff("nn",Now(),[CmpltTime])

After running the query, that new field says this: #Func!

What am I missing?

 

[YNWA]

Use "n" instead of "nn"

Also is your field storing just a time or a date and time?

 

[YNWA]

Or try here: http://office.microsoft.com/en-us/a...sed-HA001110218.aspx?CTT=5&origin=HA001054662

 

[skwilliams]

I think we have it now.

Thanks for the help

 

[skwilliams]

My only problem now is the criteria.

<"-60"

It removed some records from displaying such as a -82 but I have two records with -108 that are still showing.

Any ideas???

 

[pbaldy]

You wouldn't want the quotes in there, as the DateDiff should be returning a numeric value.

 

[skwilliams]

I get the same exact result without the quotes.

 

[skwilliams]

I've attached a zip file with a word document of screenshots showing the query design and results to better illustrate.

 

[skwilliams]

I got it. It should have been > -60 since the values are negative. Thanks for the help!

 

[pbaldy]

Glad you got it sorted out.