I m trying to read highest value of a data in a column thur below statement. But it counts physical record number. I placed one unbound field called MatTxtmax that is used for further process. (All other fields on header form are unbound)
Dim db As DAO.Database
Dim Rt As DAO.Recordset
Dim IMax As Integer
Dim NSSQL As String
Set Rt = CurrentDb.OpenRecordset("Select count(EntryNo) As Imax from T_MatRecvHeader")
Me.MatTxtmax = Rt.RecordCount
NSSQL = "Select * from T_SOHeader where EntryNo = " & MatTxtmax
Me.Form.RecordSource = NSSQL
'After this I place other code to read fields.....
Purpose is just to get the last (highest record detail) on the form once it is opened. Hence all this above code is in After Open event of header form.
The table containes 1 record but its number is 0 (data is zero in EntryNo field). I need the code to read 0 number as highest value in EntryNo field where as it show 1 in MatTxtmax unbound box.
Is there any other way or this will ok and need some alteration?
The count() function returns the number of records (i.e. 1) in the table, The field value in this record is 0. Therefore EntryNo cannot equal MatTxtmax. You can try substituting max(EntryNo) instead of count(EntryNo). This will find the largest values of EntryNo, even if it is zero. (I assume you are always trying to find the latest record entered, EntryNo is an autonumber or coded always to produce a number bigger than the previous values of EntryNo).