Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77

    Unanswered: null in max function of SQL

    SELECT max(modelNum) as modelNumber from tblm_model

    If I want to select the max of one field from a table:
    I canít find it when there is no record. I use isnull(rs.fields(0) or rs.fields(0) < 1
    to test it, but canít find itís nothing. But later on, it will give me error invalid use of null,
    how do I solve this problem? Any help is appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can use the Nz function to change the Null to a 0.

    Max(Nz([modelNum],0))

    It will return a 0 if nothing was found.

  3. #3
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    thanks for your reply.


    This one works perfect for there is record and column is null,
    But i have question is: if there is no record, it can't test this way.

    i have no record in the table, how can i find it?

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Check the .RecordCount property of the recordset. If it =0 then there are no records. If it is 1 there is at least one record in the recordset.

Posting Permissions

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