Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80

    Just-for-Curiosity DMAX() Question

    I was just writing a procedure where I needed to grab the max date from a table and store it in a variable for later use. The table is a fairly large one, with a continuous string of dates from many years ago right up to yesterday (10/5). I first tried to use a DMAX() function, but for some reason the result stopped several years back, around the end of 2015. I gave up on that and used a recordset approach with basically the same select criteria, which gave me one record with the correct maximum date.



    So my problem is solved, albeit with rather more code than I'd have needed with DMAX. but I'm left scratching my head about why DMAX gave me a bogus answer. Is there some limitation on the number of records it can crunch? Haven't seen anything in HELP about that. The table in question has records going all the way back to 1980, almost 14000, so I could imagine running up against some kind of ceiling.

  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
    14,000 records is just a 'piddling' amount, hardly a drop. I used it on a table of 60,000+ records and it executes in a snap. 60,000 is still a small dataset.
    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
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's the data type of the field? What were the two criteria? As June7 says, 14k records is trivial and certainly not the culprit.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The dates are probably in a text field. Dates in text fields can be interpreted as floating point numbers with exponents (E).
    Thus 01/01/2017 is actually greater than 01/01/2018 from a text standpoint.
    If that's not it, then it's a good mystery.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Yeah, I really didn't think it was sheer record/table size, my understanding is Access in general can handle up into the millions though I've never gone there. But in BOTH cases the criterion was simply "where this field contains a date, run through it and give me the max date." That date test was needed because it's actually a linked Excel file, and some of the rows contain other-than-dates in that field, such as Excel column headings or even some blanks. So I was asking "Look at everything in column 1 that IS a date, and give me the maximum." Worked perfectly as part of a DAO recordset, failed as described when tried the exact same thing using DMAX(). Note that "failed" doesn't mean "didn't do anything" -- it gave a result, but it was off by about a thousand days! So, since there are limits to things like string variable sizes, number and size of object names, etc., was just wondering if Mickeysoft maybe slipped one in on the DMAX function. As I said, haven't found anything browsing through the help files.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would some rows be column headings? Null fields are ignored.

    So post the attempted expression. Maybe something like:

    DMax("fieldname", "tablename", "IsDate([fieldname])")
    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.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMHO, that could effectively turn dates into text data type. Then I would expect the reason to be as I stated.
    maybe we could play with a copy of the db?

  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
    Actually, the linked Excel file.
    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.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Both would be best. There might be some code or sql that is involved as well.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Ellpee View Post
    Yeah, I really didn't think it was sheer record/table size, my understanding is Access in general can handle up into the millions though I've never gone there. But in BOTH cases the criterion was simply "where this field contains a date, run through it and give me the max date." That date test was needed because it's actually a linked Excel file, and some of the rows contain other-than-dates in that field, such as Excel column headings or even some blanks. So I was asking "Look at everything in column 1 that IS a date, and give me the maximum." Worked perfectly as part of a DAO recordset, failed as described when tried the exact same thing using DMAX(). Note that "failed" doesn't mean "didn't do anything" -- it gave a result, but it was off by about a thousand days! So, since there are limits to things like string variable sizes, number and size of object names, etc., was just wondering if Mickeysoft maybe slipped one in on the DMAX function. As I said, haven't found anything browsing through the help files.
    In VBA code, dates need to be in mm/dd/yyyy or yyyy-mm-dd format or you will get inconsistent / wrong results.
    e.g. 09/03/2018 will be read as 3 Sept 2018 when (in the UK) it means 9 Mar 2018. However 13/03/2018 will be read correctly as it can only mean 13 Mar 2018

    Unfortunately using Format to convert dates turns them into text strings as Micron has already mentioned!
    To be absolutely safe, try using CDate(Format(DateField),"mm/dd/yyyy") as part of your DMax function
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    I sometimes find it frustrating here to get "post your code, post your code, upload your database" for even simple questions. My "code", as in the parameters for what I'm asking, is identical for both DMAX() and the DAO.Recordset approach. The former produces a false result, the latter works spot on. So all I was asking in the first place is, does anyone know of any limitation built into the DMAX() function that might account for this. Apparently, no, so let's shut this thread down. (BTW, though, June7's

    So post the attempted expression. Maybe something like:

    DMax("fieldname", "tablename", "IsDate([fieldname])")


    is pretty much exactly what I did in both cases.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You could have saved yourself and those answering some frustration if you had just posted the DMax expression you used and the DAO recordset that worked.
    As it is, we're no wiser.
    If it had been possible for the very experienced forum members involved to have answered without making such requests, they would have done so by now.
    The requests are made where it would speed up providing a solution.

    Good luck with your project.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Thanks for the snark. When I ask "Is it Tuesday," I don't expect to be asked to post my calendar algorithm. All I asked in the first place was, does anyone know of any Microsoft limitations that would cause DMAX() to produce a faulty result. If answering that requires posting a bunch of code, much less uploading my client's entire database, guess I'm fishing in the wrong pond.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    My comments weren't being snarky in any way. It was stating the obvious

    But in answer to your question, DMax will give the wrong result if the expression entered is incorrect.
    So have a look at your expression and work it out for yourself as you seem so unwilling to let the expert volunteers here assist you efficiently
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    If expression works with DAO recordset but not with DMAX(), the problem resides with something different in the DMAX function, hence my question but not with my expression per se. Have a good day.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Just a "Curiosity" Question
    By MaineLady in forum Access
    Replies: 2
    Last Post: 02-28-2015, 01:12 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
    By Jrw76 in forum Forms
    Replies: 6
    Last Post: 01-09-2014, 02:47 PM
  4. DMax Question
    By The Professor in forum Forms
    Replies: 3
    Last Post: 02-05-2013, 08:30 AM
  5. Replies: 6
    Last Post: 07-24-2012, 03:02 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