Select max date and time

Posted on 2009-02-19
Last Modified: 2012-05-06
How can I select the max date and time from 2 seperate fields?  1 field has the date and 1 field has the time.

Right now, I am sometimes getting a criteria mistmatch error.

Here is my query:
SELECT qryCurrentLocationRailTrace.OrderID, qryCurrentLocationRailTrace.ContainerNumber, qryCurrentLocationRailTrace.RailRoadID, qryCurrentLocationRailTrace.Date, qryCurrentLocationRailTrace.Time, qryCurrentLocationRailTrace.City, qryCurrentLocationRailTrace.State, qryCurrentLocationRailTrace.Zip, qryCurrentLocationRailTrace.Remarks

FROM qryCurrentLocationRailTrace

WHERE (((qryCurrentLocationRailTrace.OrderID)=[pOrderID]) AND ((CDate([Date] & " " & [Time]))=(SELECT MAX(CDate([Date] & " " & [Time])) FROM qryCurrentLocationRailTrace AS qryCurrentLocationRailTrace1 WHERE qryCurrentLocationRailTrace1.ContainerNumber = qryCurrentLocationRailTrace.ContainerNumber)));

Open in new window

Question by:michael1174
    LVL 92

    Expert Comment

    by:Patrick Matthews
    SELECT OrderID, ContainerNumber, RailRoadID, City, State, Zip, Remarks, Max([Date] + [Time]) AS DateTime
    FROM qryCurrentLocationRailTrace
    GROUP BY OrderID, ContainerNumber, RailRoadID, City, State, Zip, Remarks
    HAVING OrderID = [pOrderID]
    LVL 48

    Accepted Solution

    Just add Date and Time and use as one field:


    LVL 2

    Author Closing Comment

    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome! But I think Matthew provided a full solution.


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now