Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012

    Unanswered: Type mismatch error on DMax function using dates

    Been awhile since I've asked a question! (Must mean I'm getting OK at this whole VBA programming thing )

    Anyway I've got a type mismatch that I can't figure out.
     dMaxLstReq = DMax("reqNumb", "FlightLog", "Month([txtDate])='" & frmMonth & "'" And "Year([txtDate])='" & frmYear & "'")
    In plain english: Select the highest value in the field named reqNumb from FlightLog where the Month of txtDate is equal to the variable frmMonth and the year of txtDate is equal to the variable frmYear.

    txtDate is a Date/Time field in the table FlightLog

    frmMonth and frmYear are both integer variables that take the system time (sysTime) and determine the month and year: I.E.

    frmMonth = Month(sysTime) & frmYear = Year(sysTime)
    Am I correct in thinking that the fact that my variables are integers and not times, that this is the cause of the mismatch?

    How would one go about fixing this?
    Last edited by Pis7ftw; 10-17-13 at 03:55. Reason: clarified the plain english.
    Version: Access 2010

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    it could be dates are delimited using # not ' (which delimits text/string)
    mind you ity coudl also be that the result of month and/or yuear is a number and you are trying to comapre to a test or string

    do yourself a favour
    learn how to use the debugger built into the IDE
    put a breakpoint on the code, then run it. the code will stop where you set the breakpoint

    you cnaz then examine any variables/properties and so on
    you can alter variables
    you can examine expessions
    eg putting a question mark in front of something (a varaible or expression) in the immediate window will display that variable or that expression.
    eg ?Month([txtDate])='" & frmMonth & "'" And "Year([txtDate])='" & frmYear & "'"

    if you are that confident/cocky that you are getting OK at theis VBA thing, then its time to bone up on your debugging skills
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2012

    "OK" was most definitely a relative term. Trust me, if I were confident and cocky I wouldn't be here asking for help. Also, I would have used more confident/cocky terminology in reference to my skills rather than just "OK". Perhaps I used the wrong emoticon to express my sarcasm. Maybe :P would have been more appropriate.

    I've been using the IDE and breakpoints. Turns out it was the single quotes throwing everything off. The variables were fine in how they were defined. Added Nz because it didn't like the null usage.

    dMaxLstReq = Nz(DMax("reqNumb", "FlightLog", "Month([txtDate])=" & frmMonth & " And Year([txtDate])=" & frmYear & ""), 0)
    Version: Access 2010

Posting Permissions

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