Last record modified?

[WalterInOz]

I need to able to quickly find which the last records that have been modified. I have an invisible field "txtModified" on the main form and selecting when changes where made to the record on the main form is easy using a qry on that field. The problem I face is that the form with all details of a particular item contains 2 subforms. Changes to data on each of these subforms doesn't effect the time/date on the main form. However, each of the subforms is also time stamped upon change.

The qry to select the last records changed therefore needs to look at 3 fields and select records with the latest date in any of the 3 "Modified" fields. How do I do that? I suppose I'll have to put an expression in the qry?

Thanks for your advice.
Walter

 

[WalterInOz]

I really need some advice here. Could someone point me in the right direction please? I may need to explain the problem a bit better.

I currently have the 3 tables in one query. The qry has 3 "record modified at" fields that sort the records in decending order. My problem is that the qry overall places the records that are sorted in the first field first,and subsequently sorts the 2nd and 3 field of the qry. It therefore doesn't really look for the latest modified record, it just selects the latest modified record that is in column 1 and ignores the other 2. I need to find a way in which it compares the dates in the 1st, 2nd and 3rd field of the query but have idea how to do that.

Any help is appreciated.

 

[WalterInOz]

Please help, I'm in real trouble here

I cannot believe that nobody can give me a tip on how to do this query.

 

[The_Doc_Man]

Try this line of thought:

Your sub-forms have sub-tables (child tables?) related through the parent table's prime key, perhaps? If so...

Build a UNION query that returns the parent table's prime key and the date field regardless of the fact that in the child tables, the parent's PK is just a foreign key. (Access doesn't care.)

SELECT RecID,ModDate FROM parent-table UNION
SELECT ParRecID,Child1ModDate FROM Child1 UNION
SELECT ParRecID,Child2ModDate FROM Child2;

Now write another query to sort this UNION query in date order descending. The first record shows you the most recent change.

 

[Fonz]

I presume you have in some way linked the 3 tables in the query such that all 3 date fields are "visible" to your query and your query has 3 dates for each row. If this is so, then what you need to do is convert each of the dates in each row, to its date serial number and select for each record, the highest of the date sernial numbers. To achieve this, copy (and paste) the following functions I wrote, into a MODULE in your DB. These functions are written specifcally for 3 dates.
--------------------------------------------------------------------
Function FindMaxNo(No1 As Long, No2 As Long, No3 As Long) As Long
Dim MaxVal As Long

If No1 > No2 Then
MaxVal = No1
If No3 > No1 Then
MaxVal = No3
End If
Else
MaxVal = No2
If No3 > No2 Then
MaxVal = No3
End If
End If
FindMaxNo = MaxVal

End Function

Function DateNo(xDate As Date) As Long
If IsNull(xDate) Then xDate = Date
DateNo = xDate - #12:00:00 AM#

End Function

---------------------------------------------------------------------------
Now in your query create a NEW calculated field (column) like:

SortKey:FindMaxno(DateNo([YrModDate1]),DateNo([YrModDate2]),DateNo([YrModDate3]))

Set the Sort criteria for this column to be DESCENDING and DON'T have ANY OTHER sort criteria. Run the query and the Last modified date of the 3 dates will be assigned a number and this column will sort in descending order
The field [YrModDate1,2,3] are the names of your date fields in your queries.
Note: That where one or all of the date fields in a record are null (blank), it is assigned today's date (which will have the highest date serial no) and will therefore be sorted at the top of the query.

All the best
Mark "down under too"

 

[WalterInOz]

Hi Doc_Man & Fonz,


Doc_Man, I've not been able to get your suggestion to work. I think I understand what your doing but my skills aren't good enough to make up last bit. I've ordered a few books to finally get a better understanding of what I should be doing.

What Fonz suggests works like a charm.

Thank you botrh very much for your help.