Select second record from subform

[stephen81]

I have a form with a date field called Date_Entered. This fills in automatically with the date the record was created.

I've also got a sub form for Visits made which links to the main form by a customer ID. The table behind this has Customer_ID and Visit_Date as fields.
Each time a visit is made, the user fills the date in the next row on the subform.
I also have three other text boxes on my main form. The first is First_Visit_Due which is calculated as Date_Entered + 2 weeks.
The second text box is Second_Visit_Due which takes the first visit date from the subform and adds two weeks to it. I've done this with Min() to get the earliest date related to that record.
The third text box is where I have my problem. This is Third_Visit_Due and I want this one to take the second earliest date in the subform and add two weeks to that but I don't know how to refer to that record. I can't use Max() because there may be more visits taking place afterwards.

Can anyone help?

 

[neileg]

You could use Min() again in a query but add a criterion that the date is greater than the minimum date. Find the minimum date using DMin(). I haven't tried this, and there may be better ways, but I think this should work.

 

[Rich]

does your table have a sequential key field?

 

[stephen81]

The main table (Cust_Rec) has Cust_ID as an autonumber primary key and the Visits table (Visit_Rec) has Cust_ID (linking records to Cust_Rec) and Visit_ID which is another autonumber field. So for one customer, the visits table might look something like

Cust_ID Visit_ID Visit_Date
1 1 Date1
1 4 Date2
1 6 Date3
1 17 Date4

BTW if anyone can tell me how to set tables out in my postings that would be great!

 

[stephen81]

Just to let you know, Neileg your method did work. Thanks.

I would be interested to know of any other methods though.

 

[Rich]

Article ID: Q101081