Sorting datefrom DateTime field

[Gregvg]

I have a field on a table that is a DateTime format and it needs to be that way. I am trying to query that table for all the dates to use in a combobox but I only wat the date not the time. The problem is when I format the field to just date I can't sort it anymore as a date and I can't group them. I still get multiple lines in my combobox because there multiple times for the same day. what is the proper way in a query to extract just the date from a field and still sort it as date ie 1/12/2015 is greater than 12/15/2014?

 

[MarkK]

Check out the DateValue() function. It chops the time off the date, and just returns the date portion. So your query could be . . .

SELECT DISTINCT DateValue(MyDate) FROM MyTable

 

[Gregvg]

I am getting a data type mismatch which i don't understand because the field is formatted as datetime

 

[MarkK]

I can't troubleshoot that without the code.

 

[Gregvg]

Here is the SQL statement
SELECT DISTINCT DateValue([Chemical Usage Log]![application_date_time]) AS [Date]
FROM [Chemical Usage Log];

 

[MarkK]

You don't use the "!" (exclamation mark, or bang) in SQL. I would also start naming things without embedded spaces . . .

SELECT DISTINCT DateValue(ChemUsage.ApplicationDateTime) AS [Date]
FROM ChemUsage;

 

[Gregvg]

yes it's a bad habit of mine. that didn't correct it but I think that the problem is in the data itself. I see 2 rows that aren't formatted as datetime. I will clean them up and see if that corrects it. Taking out the exclamation point didn't change anything. I'm not the one populating the database and there's been a bit of a learning curve.

 

[Gregvg]

Thank you for your help. The DateValue function did the trick. My problem was in the dataset. once I cleaned it up the error went away. Now I just need to make sure it can't happen again. Thanks again for your help.

 

[MarkK]

Yeah, you bet!