Latest Date Record Retrieval

[ccg_0004]

I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:

SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));


But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!

 

[ColinEssex]

If there are multiple tool numbers with the same (latest) date - what is the criteria for identifying the latest one?

Sounds to me that there are multiple answers to the query so Access is asking for further clarification.

Col

 

[ccg_0004]

If there are multiple tool numbers with the same (latest) date - what is the criteria for identifying the latest one?

Sounds to me that there are multiple answers to the query so Access is asking for further clarification.

Col

Yes, I have the Parameter Pop-up occuring in order to filter out all tools except the one entered into the parameter box. It should work as a criteria for the query.
Thanks for responding...

 

[ColinEssex]

So if you have say 7 tool numbers with the same latest date - you'll need to have a further identifier to limit it to one.

I suggest date and time. Try changing the date format.

Col

 

[ccg_0004]

Thanks for the help, I'm gonna keep working on it!
Do you know when I sort dates as Descending, years 2007 are coming before years 2008?