Compare dates in access table

[dmkolb]

I need to compare a date in an access table to the current date and then put the difference, in days, into the table for use in querying the table later to find 30,60,90 days late, etc. I can compare the dates in a form, but I can't seem to get that info into the table. Any ideas how to get the same info using the table, or get the form info to save in the table? Thanks.

 

[PaulBricker]

The best way to look at calculated values is in a query. That's what they are designed for. Tables basically hold static data and queries are used to create dynamic data such as what you want. Just create the query, do your calculations in one of the columns and you will have everything you need.

Paul

 

[dmkolb]

I've tried doing the calculations in the query but for some reason the query won't save and it won't close? On which line in the query should I do the calculations?

 

[PaulBricker]

To calculate days late you would put an expression like this on the Field line of a query.

DaysLate:IIf(DateDiff(&quot;d&quot;,[DateField],Date())>90,90,IIf(DateDiff(&quot;d&quot;,[DateField],Date())>60,60,IIf(DateDiff(&quot;d&quot;,[DateField],Date())>30,30,IIf(DateDiff(&quot;d&quot;,[DateField],Date())<30,&quot;Less than 30&quot;))))

If you want to simplfy it and send the value out to a function you would put this function in a module

Function mydte(dte As Date) As Variant
mydte = Switch(DateDiff(&quot;d&quot;, dte, Date) > 90, 90, DateDiff(&quot;d&quot;, dte, Date) > 60, 60, DateDiff(&quot;d&quot;, dte, Date) > 30, 30, DateDiff(&quot;d&quot;, dte, Date) < 30,&quot;Less than 30&quot;)

End Function

and then on the Field line of your query you would put

DaysLate:mydte([DateField])

Either of these will return the number of days overdue. It will not write it to a table. One reason you don't want to write something like this to a table is the value can change every 30 days. If you write a 30 day overdue value to the table, after 60 days, the info will not update in the Table unless you run code. In the query, as soon as the date becomes 60 days overdue, the query will compute it with out making any adjustments.

Paul

 

[dmkolb]

Thanks Paul but I got that part working now. Now I have to sort the database on Days Late. How can I create a table based on a query, so I can then sort it by days late?

 

[PaulBricker]

Why not just sort the query on the DaysLate field. If there is some other reason not to, then you can create a Make Table query that will put the data into a Table but anything you can do in a table, you can do with a query with half the work.

Paul