Locating the Most Stale (Oldest) Contact

We will now make use of our expertise in (a) building Command Buttons and (b) our inherited values for the FollowUp field.

Build a Command Button; name it "cmd_ Oldest" and set its caption to be "Oldest". You can Cancel out of the wizard immediately - we will be building our own code here.

The purpose of this button will be to throw up on the screen the oldest, or stalest record in our table. This answers the question "Who haven't I spoken with for the longest time? Who deserves a telephone call right now?".

Private Sub cmd_Oldest_Click()
Me.OrderBy = "FOLLOWUP"
Me.OrderByOn = True
Me.Filter = "FOLLOWUP <= '" & Format(Now(), "YYYYMMDD") & "'"
Me.FilterOn = True
DoCmd.GoToRecord , , acFirst
End Sub

Remember that the reserved word "Me" indicates "where we are right now", which is (or will be when we are running the form), our user form frmClient.

The OrderBy property

…tells Access that we want the form to display records in sequence (ascending, by default) on the FOLLOWUP field; the FOLLOWUP field is our eight-character field laid out as YYYYMMDD, so we will be seeing our records displayed with the lowest value first, and that means the earliest year.

The OrderByOn property

… activates the sequence.

The Filter property

… tells Access to filter out records that we do not want to see, in particular, this filter command tells Access that we want to see ONLY those records for which the Follow Up date is less than or equal to the date right now, expressed as an eight-digit character string.

We tell Access to activate the filter which we have defined.

When that is done, we ask Access to bring to us the first record in the stated sequence, that is, the oldest record in terms of Followup date.

Search