Solved

ms access date sort

Posted on 2012-03-12
1
368 Views
Last Modified: 2012-06-21
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");
0
Comment
Question by:johnmadigan
1 Comment
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37711282
Try formating your dates as yyyy-mm-dd for proper sorting
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Cisco RV042 router is a popular small network interfacing device that is often used as an internet gateway. Network administrators need to get at the management interface to make settings, change passwords, etc. This access is generally done usi…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

786 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