Dawn- Change your SQL to this:
strSQL = "SELECT CalHist.equip_num, CalHist.cal_date, " & _ "CalHist.auto_adjust, CalHist.as_found, CalHist.cal_interval " strSQL = strSQL & "FROM CalHist " & _ strSQL = strSQL & "WHERE (CalHist.equip_num = " & _ [Forms]![Device Master]![equip_num] & _ ") AND (CalHist.cal_date IN " & _ "(SELECT Top 3 cal_date FROM CalHist As CH2 " & _ "WHERE CH2.equip_num = " & [Forms]![Device Master]![equip_num] & _ "ORDER BY cal_date DESC)" Then just Do Until rs.EOF. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: ms_access@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Crosier, Dawn Sent: Tuesday, November 15, 2005 4:51 PM To: ms_access@yahoogroups.com Subject: [ms_access] Return Last three Records I have a requirement for a form that I am not sure how to tackle. So, some advice please. The requirement is to have the form opened ready for a new record, BUT they want one of the fields to be calculated based on the last three records and values found in those records. So I believe that what I need to do is open the form, and create a function that will determine the default value of the new record. The below code is my first stab, and I don't know for sure how to get the last three records. Function CalibInt() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim i As Integer Dim CalibInterval As Integer strSQL = "SELECT CalHist.equip_num, CalHist.cal_date, CalHist.auto_adjust, CalHist.as_found, CalHist.cal_interval " strSQL = strSQL & "FROM (Vendors RIGHT JOIN CalHist ON Vendors.name = CalHist.vend_name) LEFT JOIN Methods ON CalHist.cal_method = Methods.meth_name " strSQL = strSQL & "WHERE (((CalHist.equip_num)=[Forms]![Device Master]![equip_num]))" 'Now find the last three records. If rs.Count > 3 Then i = 3 Else: i = DCount(rs) End If Do While ??? '- for last three records 'Determine the value of fields If rs![auto_adjust].Value = True And i = 3 Then If rs![As_Found].Value = "AC" Then intas_found = intas_found + 1 rs.MoveNext Loop End If Else If rs![auto_adjust].Value = True And i < 3 Then CalibInterval = rs![cal_interval].Value End If If rs![auto_adjust].Value = False Then CalibInterval = rs![cal_interval].Value End If CalibInt = CalibInterval End Function Dawn Crosier Application Specialist "Education Lasts a Lifetime" [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
