Sorting by Short Date without hours, minutes, and seconds.

[5pac3m0nk3y]

Hi,

I got a linked table with a field called ENDDATETIME displaying the date and time. I need to run a query that displays ENDDATETIME occurring on a date such as 1/1/2017 but ignoring the hours, minutes, and seconds.

The problem is that I can't find records by only the date. So if I want to find an end date time occurring on 1/1/2017, I have to include the precise hours, minutes, and seconds.
I went into the table design properties and chaned the format and input mask but I'm barking up the wrong tree.

What should I do to enable querying by only the short date while ignoring the time?

Here is the SQL

SELECT CLASSROSTERVIEW.CLASSSESSIONID, CLASSROSTERVIEW.COURSE_ID, CLASSROSTERVIEW.ENDDATETIME, CLASSROSTERVIEW.COURSETITLE
FROM CLASSROSTERVIEW
WHERE (((CLASSROSTERVIEW.ENDDATETIME)>=#5/22/2009#));

If I remove the greater than sign, I get nothing because of the ENDATETIME is in this format (for example): 4/14/2015 4:00:49 PM

Thanks,
Al

[ranman256]

format(ENDDATETIME,"mm/dd/yyyy")

[5pac3m0nk3y]

HI, I answered my own question. I found the solution here: https://www.techonthenet.com/access/...ate/format.php

ENDDATE: Format([classrosterview.enddatetime],"dd/mm/yyyy")

Now my query shows the dates in a format I can use. I wanted to get rid of the hours, minutes, and seconds and search by precise dates minus the time.

[pbaldy]

The downside to that solution is that formula must be applied to all records in the table. I'd probably use Between and add a day or 23:59:59 to the desired end date.

[5pac3m0nk3y]

The downside to that solution is that formula must be applied to all records in the table. I'd probably use Between and add a day or 23:59:59 to the desired end date.

Good point. The problem is they need to select precise dates to get a list of classes during that time to run a mail merge emailing all the people in those classes.

The other thing I noticed too that even though my query now shows the dates without time, I still can't sort by =#1/1/2017#.

Maybe I have to create a query that updates a table and then change the format in that table, and run the query against it?

[5pac3m0nk3y]

Thanks for your reply. This works as far as how the query results look, but I can't sort by date only even after formatting the query to show only the dates. I'm guessing maybe I have to create an update query that populates a seperate table which I can then run the query against.

[pbaldy]

I'm not clear on the problem. I'd have two textboxes, for start and end date. In the query or in code, add a day or 23:59:59 to the selected end date entered by the user.

I'm also not clear on what you mean by "can't sort by = #1/1/2017#". You would sort by the date field. Your format function is turning that to text, so you'll get an alphabetic sort, if that's what you're seeing. Another reason to leave the field alone. You can use DateValue() instead of Format(), though you'd still face the potential performance problem.

[5pac3m0nk3y]

I'm not clear on the problem. I'd have two textboxes, for start and end date. In the query or in code, add a day or 23:59:59 to the selected end date entered by the user.

I'm also not clear on what you mean by "can't sort by = #1/1/2017#". You would sort by the date field. Your format function is turning that to text, so you'll get an alphabetic sort, if that's what you're seeing. Another reason to leave the field alone. You can use DateValue() instead of Format(), though you'd still face the potential performance problem.

--------------------


Hello again!

Thanks for your comment. I appreciate your help.

There are two columns I'm working with in a query that gets its parameters from a form I created. The user enters in the startdate and the endate, presses a button that runs a query to pull up a list of all enrollees needing to complete a given course within the given time span.

This query also provides list of email addresses to be used in a mail merge to email all of those enrollees with incomplete course/s.

The program they use for reporting is called Training Manager. It does not have a report function like the one I'm making. Instead they have to print ALL the users both completed and not complete, sort them by hand, then create a list of people to email. Then the add each email in one at at time by hand. LOL!

So let's say I want the one course that is set to occur between 2/23/2017 and 3/3/2017. If I put in 2/23/2017 as the startdate and 3/3/2017 as the end date,in Training Manager, I get one course called staff wellness that occurs exactly between those days.

Let's say you put the date parameters for startdate and endate into the query in design view without relying on the form; the dates I just mentioned would come up as >=#2/23/2017# and <=#3/3/2017#

You will get 0 results. If I add a day either before or after the dates mentioned, you will get multiple instances of the same course since they are reoccurring. One course starts, the next ends. They are seperate not by days but hours, minutes, and seconds.

Here's the SQL for my query:

SELECT CLASSROSTERVIEW.STARTDATETIME, CLASSROSTERVIEW.ENDDATETIME, ENROLLEDSTUDENTSVIEW1.PERSONNELNUMBER, ENROLLEDSTUDENTSVIEW1.PERSONNELDISPLAYNAME, ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY, COURSECATALOG1.COURSENUMBER, COURSECATALOG1.COURSETITLE, PERSONNELVIEW2.EMAILADDRESS, ENROLLEDSTUDENTSVIEW1.COMPLETIONDATE
FROM CLASSROSTERVIEW INNER JOIN ((COURSECATALOG1 RIGHT JOIN ENROLLEDSTUDENTSVIEW1 ON COURSECATALOG1.COURSEID = ENROLLEDSTUDENTSVIEW1.COURSEID) INNER JOIN PERSONNELVIEW2 ON ENROLLEDSTUDENTSVIEW1.PERSONNELNUMBER = PERSONNELVIEW2.PERSONNELBARCODE) ON CLASSROSTERVIEW.PERSONNELID = ENROLLEDSTUDENTSVIEW1.PERSONNELID
WHERE (((CLASSROSTERVIEW.STARTDATETIME)=Forms!FRM_EMAIL_ TRAINEES_BY_DATE!txtBeginOrderDate) And ((CLASSROSTERVIEW.ENDDATETIME)=Forms!FRM_EMAIL_TRA INEES_BY_DATE!txEndOrderDate) And ((ENROLLEDSTUDENTSVIEW1.STATUSDISPLAY)="Enrolled") And ((PERSONNELVIEW2.EMAILADDRESS) Is Not Null) And ((ENROLLEDSTUDENTSVIEW1.COMPLETIONDATE) Is Null));


Hope this makes things clearer.

Best regards,
Al

[5pac3m0nk3y]

Ranman256,

This changes the format of the linked table visually, but the query is still unable to use shortdate as the parameter. It still 'considers' HH:MM:SS.

I'm basically inserting date parameters into a query via form. The query is checking against several linked tables from a source database I cannot alter. The source database is the backend of a program called Training Manager which does not allow users to create a report showing all users who have not completed a given course during a particular time span. I'm creating a query that is to be used in a mail merge to email all enrollees who have not completed a course withing a particular time span.