Results 1 to 9 of 9
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Trying to get highest number in a field (LoadTested) for todays date.

    I have this in the criteria box under the LoadTested field but it is only giving me the highest TestedDate which is another field.

    Code:
    =DMax("LoadTested","tbl_343sTested","[TestedDate]=" & Date())
    Could someone help me figure out why it is not giving me the proper output.
    Here is the entire SQL



    Code:
    SELECT tbl_343sTested.FixturePosition, tbl_343sTested.SerialNumber, tbl_343sTested.TestedDate, tbl_343sTested.Technician, tbl_343sTested.TankTestedIn, tbl_343sTested.LoadTested, tbl_343sTested.PreReading, tbl_343sTested.HighReading, tbl_343sTested.PostReading, tbl_343sTested.Recheck, tbl_343sTested.PassFail, tbl_343sTested.Tr343Id, tbl_343sTested.MeggerID, tbl_343sTested.PressureIndID, tbl_343sTested.Comment
    FROM tbl_343sTested
    WHERE (((tbl_343sTested.LoadTested)=DMax("LoadTested","tbl_343sTested","[TestedDate]=" & Date())));

    Thank you
    Walker

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A bit confusing. If you have that expression as the textbox source, what does the sql statement have to do with it? Or is the entire sql the textbox source?
    By "criteria box" do you mean the row source for a textbox? Or it's record source, or neither?
    I do not know why you would get a TestedDate value when you are asking for LoadTested with
    Code:
    =DMax("LoadTested","tbl_343sTested","[TestedDate]=" & Date()
    You should be getting the max LoadTested value from the table where TestedDate = Date(). If there are several date values that are equal to Date(), I'm pretty sure you'll get the first record using DMax. Maybe that's what you mean? If so, I think Date() may not be a suitable criteria, or TestedDate may prove to be inadequate as a field to search on.
    Last edited by Micron; 02-07-2017 at 05:57 PM. Reason: additional info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think the construction of the DMax criteria is a little off.
    Whether using the DMax as a control's control source or part of a WHERE clause, I would use
    Code:
    =DMax("LoadTested","tbl_343sTested","[TestedDate]= #" & Date() & "#")
    Dates must be delimited with the hash marks....

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    At first I thought "Good catch". Then thought should we have to delimit the date when it's returned by a function? Turns out the problem is the quotes. In the immediate window, this works ?DMax("RequestDate","RequestT","RequestDate < Date()")
    but not this form
    "[TestedDate]=" & Date()
    We're forgetting that the entire parameter has to be within quotes.
    Last edited by Micron; 02-07-2017 at 06:42 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664


    If you use the query designer, This should work:
    Code:
    SELECT tbl_343sTested.FixturePosition, tbl_343sTested.SerialNumber, tbl_343sTested.TestedDate, tbl_343sTested.Technician, tbl_343sTested.TankTestedIn, tbl_343sTested.LoadTested, tbl_343sTested.PreReading, tbl_343sTested.HighReading, tbl_343sTested.PostReading, tbl_343sTested.Recheck, tbl_343sTested.PassFail, tbl_343sTested.Tr343Id, tbl_343sTested.MeggerID, tbl_343sTested.PressureIndID, tbl_343sTested.Comment
    FROM tbl_343sTested
    WHERE tbl_343sTested.LoadTested = DMax("LoadTested","tbl_343sTested","[TestedDate] = #" & Date() & "#");


    If it is in VBA, then it should still work:
    Code:
        sSQL = "SELECT tbl_343sTested.FixturePosition, tbl_343sTested.SerialNumber, tbl_343sTested.TestedDate, tbl_343sTested.Technician,"
        sSQL = sSQL & " tbl_343sTested.TankTestedIn, tbl_343sTested.LoadTested, tbl_343sTested.PreReading, tbl_343sTested.HighReading,"
        sSQL = sSQL & " tbl_343sTested.PostReading, tbl_343sTested.Recheck, tbl_343sTested.PassFail, tbl_343sTested.Tr343Id,"
        sSQL = sSQL & " tbl_343sTested.MeggerID, tbl_343sTested.PressureIndID, tbl_343sTested.Comment"
        sSQL = sSQL & " FROM tbl_343sTested"
        sSQL = sSQL & " WHERE tbl_343sTested.LoadTested = " & DMax("LoadTested", "tbl_343sTested", "[TestedDate] = #" & Date & "#") & ";"
    What am I missing?

  6. #6
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Sorry for the confusion. I will try to explain a little more. I have an input form that currently stores all the information on the form upon a button click event. the problem I am having is getting the information back into the form to view or reprint the report. There are 9 records for each Load tested. like yesterday there were 3 loads tested (1,2,&3) each load has 9 records. the problem I am having is getting the query to retrieve only the 9 records from the most recent load tested. Is there any other code that would help figure this out for you all to see.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What am I missing?
    All I'm saying is that it works if you place the ending quote that was missing from the first post
    ("LoadTested","tbl_343sTested","[TestedDate]=" & Date())
    because Date() returns the date data type. You don't have to delimit Date() if it's written the way I put it. Using the OP's values and not mine, it would be:
    ("LoadTested","tbl_343sTested","[TestedDate]=" & Date()")
    Your way works too.

    Nigthwalker: start with a copy of your query but omit the WHERE part. If it returns records, we can eliminate the base query as the problem. Then look at the table and pick a date that should work and put that in this new query as a 'hard coded' value. I'd play with operators here (=, >, <) just to ensure I accept the results as valid or not for the need, but not BETWEEN. If it returns no records when run, there might be something wrong with the logic in your field choices or approach to this query. If this gets us nowhere, then based on this comment
    the problem I am having is getting the information back into the form to view or reprint the report
    might indicate an issue with the form, such as using the same form to enter and retrieve data when it has been set to allow data entry only. However, based on your posts, I take it that the query doesn't return records when you run it means that you have run it manually and are not relying on the form to do it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thank you for the troubleshooting tips Micron. that helped me figure it out. the base query was fine it was just the where part but here is the final code that worked.

    Thank you all for your help.

    Code:
    SELECT tbl_343sTested.FixturePosition, tbl_343sTested.SerialNumber, tbl_343sTested.TestedDate, tbl_343sTested.Technician, tbl_343sTested.TankTestedIn, tbl_343sTested.LoadTested, tbl_343sTested.PreReading, tbl_343sTested.HighReading, tbl_343sTested.PostReading, tbl_343sTested.Recheck, tbl_343sTested.PassFail, tbl_343sTested.Tr343Id, tbl_343sTested.MeggerID, tbl_343sTested.PressureIndID, tbl_343sTested.Comment
    FROM tbl_343sTested
    WHERE (((tbl_343sTested.TestedDate)=Date()) AND ((tbl_343sTested.LoadTested)=DMax("LoadTested","tbl_343sTested","[TestedDate]= #" & Date() & "#")));

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    welcome; glad you got it solved.

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

Similar Threads

  1. t to todays date
    By Ekhart in forum Forms
    Replies: 4
    Last Post: 07-26-2016, 10:59 PM
  2. Replies: 3
    Last Post: 06-13-2014, 09:16 PM
  3. Replies: 3
    Last Post: 07-09-2012, 05:59 AM
  4. Replies: 1
    Last Post: 12-12-2011, 06:32 AM
  5. Adding todays date
    By EDEd in forum Forms
    Replies: 2
    Last Post: 10-08-2010, 06:27 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