Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1

    Answered: DMax Looking for the closest date giving "Invalid use of Null"

    Hi again,

    I thought I'd finally understood DMax properly, and now it's thrown another wrench in the works..

    I'm trying to retrieve the highest date (Date_of_Birth) in a table range closest to a calculated variable (var_retirement_date). Date_of_Birth is defined as a Date, as is var_retirement_date, and I have a value for var_retirement_date (15/08/2004), but my DMax function -
    var_retire_date = DMax("Date_of_Birth", "TBL_Retire_age_assump_Retirement_age", "Date_of_Birth <= " & var_retirement_date & "")
    Keeps giving me the Run-time error 94 - Invalid use of Null, despite the fact it should be impossible, since even if it can't find an exact match, it should take the highest number below it. The end of the Date_of_Birth range is:
    ...
    01/01/1976
    01/11/1976
    01/04/1978
    01/08/1979
    01/01/1981

    So it should be returning 01/01/1981, right? Anyone know what I'm missing?

    Thanks in advance

  2. Best Answer
    Posted by myle

    "var_retire_date = DMax("Date_of_Birth", "TBL_Retire_age_assump_Retirement_age", "Date_of_Birth <= " & var_retirement_date & "")

    try this

    var_retire_date = DMax("Date_of_Birth", "TBL_Retire_age_assump_Retirement_age", "Date_of_Birth <= #" & format(var_retirement_date,"mm/dd/yyyy") & "#")

    You were on the right track

    as you pass a 15/08/2004 it was dividing the values first then try to look up the answer

    putting the # in tell the SQL we are dealing with a Date Value

    and SQL like its date tobe USA format"


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    var_retire_date = DMax("Date_of_Birth", "TBL_Retire_age_assump_Retirement_age", "Date_of_Birth <= " & var_retirement_date & "")

    try this

    var_retire_date = DMax("Date_of_Birth", "TBL_Retire_age_assump_Retirement_age", "Date_of_Birth <= #" & format(var_retirement_date,"mm/dd/yyyy") & "#")

    You were on the right track

    as you pass a 15/08/2004 it was dividing the values first then try to look up the answer

    putting the # in tell the SQL we are dealing with a Date Value

    and SQL like its date tobe USA format
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #3
    Join Date
    Nov 2016
    Posts
    29
    Provided Answers: 1
    Beautiful, thanks for the help, it's working perfectly now

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
  •