Results 1 to 12 of 12
  1. #1
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    286

    Critera to find the earliest time in a query field

    Good day all,

    I have a query from a table with fields "OfficerName" and "SignInTime". I want to return a single record for any particular officer containing their earliest time for the day.

    I enter the officer's name in as part of the criteria and in the SinginTime I enter Min(SignIntime) but this give an error message.



    Can anyone give some suggestions as to what to enter to get the desired results?

  2. #2
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Is your query a group by query? If not, then you can't use Min().

  3. #3
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    286
    Thanks for the response.

    Yes my Query is a Group query.

    I really want to use VBA code to make the query but Just did it in the query window to have the Sql code written for me.

  4. #4
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    286
    This is the code that I have been trying

    Code:
    DoCmd.OpenForm "frmSupervisorEditPunctuality", acNormal, , "[officerName] = '" & Me.Combo65 & "' And Datevalue([Auto Date]) = # " & DateValue(Me.Text56) & "#" & " And Min([Time Entered By Officer])"

    This is the error message: Can't have aggregate function in where clause ..............................

  5. #5
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    This is the error message: Can't have aggregate function in where clause ..............................
    The reason for that error message is, well, you can't have aggregate function in the where clause. :-)

    As I said you have to use a group by query. Your where clause is not a group by query. It's a where clause.

    Secondly, even if you could use Min() in a where clause (which you can't), your code still wouldn't work. You have as the where clause:

    Code:
     "[officerName] = '" & Me.Combo65 & "' And Datevalue([Auto Date]) = # " & DateValue(Me.Text56) & "#" & " And Min([Time Entered By Officer])"
    So, broken down into each component, you have:

    Code:
    [officerName] = '" & Me.Combo65
    Datevalue([Auto Date]) = # " & DateValue(Me.Text56) & "#"
    Min([Time Entered By Officer])
    Do you see the other problem? Your last part of the Where clause doesn't compare Min([Time Entered By Officer]) to anything.

    So, make Min([Time Entered By Officer]) a part of your query, and then you can refer to the query field that contains Min([Time Entered By Officer]) in your Where clause.

  6. #6
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    286
    Thanks. The illustration and explanations help a lot.

    So what is did was to go back to the form to be loaded and for the record source I added the dynaset with all the fields from the appropriate table.

    Hence can I now refer to the Min([Time Entered by Officer]) since I know it is now associated with the form?

  7. #7
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    You said you're using a group by query. Where, exactly, are you using a group by query? As the recordsource for your form? In that case your form won't be editable.

    In summary:

    * You can refer to fields in your form's record source in your Where clause, whether or not those fields are actually added to the form itself. They just have to be in the record source.

    * But you can't use Min() in your where clause. You have to use Min() in the record source. Then you can refer to the field that uses Min() in your where clause (by referring to the field by its name).

    Does that clarify for you?

  8. #8
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    286
    Thanks again. I am getting to know some things I was ignorant to. One thing for sure is that I now know that a group by Query is not editable.

    I manage to get what I want but the problem is that I want to be able to edit. So I need more assistance which my be contained in your explanation above. But lets simplify it further.

    Now forget about the form, cause if I get the query handled then I can deal with the form. lets consider there is just a query.

  9. #9
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    I have a query from a table with fields "OfficerName" and "SignInTime". I want to return a single record for any particular officer containing their earliest time for the day.
    I assume this is still the goal. As such, you'll need two queries: one as a group by, to get the values you want to edit, and another query which is used for editing.

    So, first, create a group by query with OfficerID and SignInTime (I assume you have an ID value for each officer, and aren't using the name as the primary key field!)

    In the query, leave OfficerID as "group by", and set SignInTime to "Min", as we've discussed. Give Min of SignInTime an alias, such as "FirstSignIn" or whatever.

    OK, save and close this query. Let's call it "FirstSignIns".

    Next, create a new query off the same table. Add the OfficerID and SignInTime fields. In the Criteria row below OfficerID, put:

    Code:
    In (Select OfficerID From FirstSignIns)
    and in the Criteria row below SignInTime, put:

    Code:
    In (Select FirstSignIn From FirstSignIns)
    Add other fields you need to the query.

    So now this second query will only return the record corresponding to the first sign in for each officer, and will be editable.

  10. #10
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    286
    Thanks Niel. This has a steep learning curve; never the less I tried to follow the instruction carefully. However my final results were not editable. But maybe I over simplify my project at this stage and so some of what you gave could not correspond with what I was actually doing. Primiraly my query was made up of table while we were referring to a single table. I will continue to play around with it some more still.

    Out of curiosity what does the "In" stand for in the query?

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,055

  12. #12
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Please post your sql code from all of your queries. (click view, and then sql)

    In() returns matches to what is inside the parentheses.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-19-2013, 12:49 PM
  2. Replies: 1
    Last Post: 12-01-2012, 12:44 PM
  3. Replies: 9
    Last Post: 06-28-2012, 10:10 PM
  4. Replies: 5
    Last Post: 06-09-2012, 12:37 PM
  5. Replies: 1
    Last Post: 02-17-2012, 04:43 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