Results 1 to 8 of 8
  1. #1
    uoghk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    149

    DMAX function Invalid use of null

    I use DMAX function and get error 94 Invalid use of null.


    My function is KeyId=DMax("PriId", "ProdPrice", "CustId=" & rst!CustId & " and PriStatus='Active'")
    In ProdPrice table, PriId is auto-number primary key.
    All PriId, CustId and PriStatus have values, none of them contains NULL.
    rst!CustId has a value 2.
    In Query Designer, I test this DMAX function, it returns records, no error is found.
    Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Suggest provide db for analysis. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make sure CustId is not null.
    and KeyID is a variant. You can’t assign null to numeric variable.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Assign the criteria to a string variable and debug.print that, then use in the function.
    That way you will see what you actually have then, not what you think you have.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with WGM. rst!CustId probably contains no value, not necessarily because the field has nulls, but because there's something wrong with the recordset code.
    You could post the whole sub/function code and indicate which line is raising the error (implied that it is your DMax line, but not stated exactly) but a zipped copy of the db would be better if you can't find the cause of the problem.
    If you post code, please use code tags (# button on posting toolbar).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So what happens id you test it like this:
    KeyId=DMax("PriId", "ProdPrice", "CustId=" & 2 & " and PriStatus='Active'")


    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    uoghk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    149
    thanks ranman256. you are right.
    originally i declare the KeyId as Long.
    After I declare it as Variant, it can hold null value return from the DMAX.
    also thanks others help.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So contrary to your assertion in OP, one of the inputs is Null and then DMax returns Null?

    Alternative is to wrap the DMax() in Nz().
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-09-2021, 08:44 AM
  2. DMAX returns Null value
    By jtan in forum Programming
    Replies: 5
    Last Post: 05-10-2019, 02:26 AM
  3. Replies: 4
    Last Post: 04-09-2019, 02:05 PM
  4. DMax returning Null
    By Markb384 in forum Access
    Replies: 1
    Last Post: 05-01-2014, 09:11 AM
  5. DLookUp function giving invalid use of null error
    By shubhamgandhi in forum Programming
    Replies: 4
    Last Post: 07-21-2011, 06:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums