Find the difference between two dates in the same field.

[Guest]

I have a date field that is populated with multiple dates. The multiple dates
come from a status change. For example, if the status is changed to
"approved" the date the change took place is entered in the date field. Later
when the status changes to "waiting assignments" that date is entered, too.
In this example, I have two dates. I want to use a query that shows records
if the days between the two dates, in the same field, is greater than 31.
Thanks.

 

[Guest]

You need to change your table structure to use datatype of DateTime field in
stead of a text field with a whole lot of numbers that Access will not
recognise as a date.

You would need a one-to-many relationship set from the item to the many
dates with the status. The 'many' table would have date, status, and item
identification.

You then use a form/subform to view the data.