Validate > Previous Value in Field?

[gimmyd]

Can someone tell me how to set the validation property for a field that insures the new value is greater than the last entry in the same field?

 

[larryww]

#$%^ing Access does not have a Max() function. This is especially productivity-sucking in this instance because UDF's are not allowed for ValidationRule.

I haven't seen anyone else give you a direct answer, so here's at least what I would do until told better: I would punt and manage input via form(s), which has a learning curve if you don't use them already. The gist of my approach would be to set up aggregate query Query1 as the max of the field, in the output field MyMax (i.e. SELECT Max(Table1.myval) AS MyMax
FROM Table1
and do validation with something like

Function MyValMax()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Query1&quot
MyValMax = rs.Fields("MyMax&quot
End Function

The value of MyValMax, your UDF, would be the highest value existing for field Table1.myval, if you set up the Query1 as above.

Of course, this could be modified to manage several fields and tables, etc. The main thing though is that I would accept new data in form input, and manage the validation there.

I hope someone gives you a more direct solution, but maybe this will be better than nothing for you.