Days Between Submittal Date and Today

[JustPunkin]

Working on a project where we want to return a results of how many days overdue a document is. We have a submittal date (which is the due date).

I want to query all overdue documents (which I already do), but in that query, or the report of the query, I want to show how many days late a document is. (Submittal Date - Today).

Can I do this in a query/report? Do I need to create a field in the table for this?

Please help - this whole date thing really has me confused for some reason.

Thanks
Brenda

 

[raskew]

Hi -

Lookup the DateDiff() function.

Here's an example using Northwind's Orders table:

SELECT OrderID, OrderDate, RequiredDate, DateDiff("d",[OrderDate],[RequiredDate]) AS Expr1
FROM Orders;

Give it a try as is, or substitute your table/fields names.

HTH - Bob

 

[JustPunkin]

Hi -

Lookup the DateDiff() function.

Here's an example using Northwind's Orders table:

SELECT OrderID, OrderDate, RequiredDate, DateDiff("d",[OrderDate],[RequiredDate]) AS Expr1
FROM Orders;

Give it a try as is, or substitute your table/fields names.

HTH - Bob

Thanks, I'll have a look at that tomorrow.

 

[roelandkim]

As mentioned, using the datediff function in a query will work. You can use date() as the second criteria, so the query always uses the current date as the comparison date. No need to create any extra fields. Just substitute you the table name and the field name (Submittal Date).

SELECT TABLENAME.[FIELDNAME], DateDiff("d",[FIELDNAME],Date()) AS DaysOverdue
FROM TABLENAME
WHERE (((DateDiff("d",[FIELDNAME],Date()))>=1));

 

[JustPunkin]

Thanks so much. DateDiff was just what I was looking for.