Results 1 to 4 of 4
  1. #1
    grant99 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    3

    DMAX Error when Null

    I'm new so please don't get too technical or yell for non-clean code.


    I have an unbounded text box that I'm trying (through Expression Builder) to get to display the date of the last note entered for a customer. It works great unless there has never been a note for the customer. If that is the case, it just bypasses that customer so I don't see they have an open ticket.
    My code for the text box is-
    =IIF(IsNull(DMax("NoteDate","Notes","[CustomerID] = Form![CustID] ")),"No Notes",DMax("NoteDate","Notes","[CustomerID] = Form![CustID] "))

    "No Notes" is what I'm trying to get to display if the Notes tables doesn't have an entry for the customer.

    Thanks.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    there is an error in your dmax criteria which may be causing the problem or it may be because your are trying to return a date datatype but then using a string if null. suggest try using the format property of the control instead

    your dmax should be


    DMax("NoteDate","Notes","[CustomerID] = " & [CustID])

    so try


    =nz(DMax("NoteDate","Notes","[CustomerID] = " & [CustID]),"No Notes")

    or for the format property just use dmax in your control source

    =DMax("NoteDate","Notes","[CustomerID] = " & [CustID])

    and in the format property put

    ;;;"No Notes"

    (can't remember if it is 2 or 3 semi colons)

  3. #3
    grant99 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    3
    I tried everything with the format field on the text box and it set all the last note dates to blank and still did not display the customers that are missing notes.

  4. #4
    grant99 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    3
    I FIXED IT! It appears that it had to do with my join instead of the equation. I changed the join between the Customer table and the note table to the #2 option instead of the default #1 that was created originally and it appears to have fixed my problem.

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

Similar Threads

  1. Invalid use of NULL error
    By CHEECO in forum Access
    Replies: 13
    Last Post: 03-16-2016, 07:20 PM
  2. DMAx Question for Report - DMax <= Dtae
    By crimedog in forum Access
    Replies: 8
    Last Post: 12-29-2014, 09:31 PM
  3. DMax returning Null
    By Markb384 in forum Access
    Replies: 1
    Last Post: 05-01-2014, 09:11 AM
  4. Replies: 6
    Last Post: 07-24-2012, 03:02 PM
  5. Error 94: Invalid Use of Null
    By athomas8251 in forum Forms
    Replies: 3
    Last Post: 11-09-2011, 11:46 AM

Tags for this Thread

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