Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

Select max date and time

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

0
michael1174
Asked:
michael1174
  • 2
1 Solution
 
Patrick MatthewsCommented:
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]
0
 
Gustav BrockCIOCommented:
Just add Date and Time and use as one field:

[Date]+[Time]

/gustav
0
 
michael1174Author Commented:
Thanks
0
 
Gustav BrockCIOCommented:
You are welcome! But I think Matthew provided a full solution.

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now