We help IT Professionals succeed at work.

ms access date sort

johnmadigan
johnmadigan asked
on
I have form with a start date and end date field.  I have a query that looks at these dates to sort the query.

I have the query group by date and format the date to show the date only (removing the time).

When I select date in 2010 it works fine - If I put dates from 2011 the records don't show up on the query.

I'm not sure what is going on.

Any suggestions?  Query below:

SELECT Format([DateStampPun],"mm/dd/yyyy") AS JobDate, JobsDoors.ProjectNo, Count(JobsDoors.ProjectNo) AS [Count]
FROM JobsDoors INNER JOIN DoorTagLog ON JobsDoors.ID = DoorTagLog.BatchID
GROUP BY Format([DateStampPun],"mm/dd/yyyy"), JobsDoors.ProjectNo
HAVING (((Format([DateStampPun],"mm/dd/yyyy")) Between [Forms]![MainForm]![SDate] And [Forms]![MainForm]![EDate]) AND ((JobsDoors.ProjectNo) Is Not Null))
ORDER BY Format([DateStampPun],"mm/dd/yyyy");
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Try formating your dates as yyyy-mm-dd for proper sorting

Explore More ContentExplore courses, solutions, and other research materials related to this topic.