Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Highest Number Thur Select Statement

    Hello,

    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")

    Rt.MoveLast

    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?

    Please extend your help.
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    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).

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Jim,

    EntryNo field is Number (not autonumber) as I was increasing it by 1 while adding new record.

    I will try with Max and then I will come back...
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Hi,

    I tried with Max(EntryNo) but it displays MatTxtMax value as 1

    NSSQL = "Select * from T_SOHeader where EntryNo = " & MatTxtmax
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    A bit improvement...


    Set Rt = CurrentDb.OpenRecordset("Select Max(EntryNo) As Imax from T_MatRecvHeader")

    'Rt.MoveLast . Removed this...

    Me.MatTxtmax = Rt!IMax ' Changed this a bit

    NSSQL = "Select * from T_MatRecvHeader where EntryNo = " & MatTxtmax

    It gives now 0 as highest record value but at the same time is asks parameter value for EntryNo.

    Why this should ask? Can someone tell me?
    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I removed previous selected value from Forms record source that was set automatically and it worked smoohly.

    Thanks for the help guys...
    With kind regards,
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •