Currently on one of my forms I have users choose the date from an ActiveX Calendar Control 11 and store it with the other data they input. My problem is when I try to sort the data by its date, they do not appear in the correct order. If someone enters data on 7/9/2007 and then enters another set of data on 7/25/2007 when I sort it by date the 7/25 entry appears first. From what I can tell Access is seeing 7/2./.... and thinking that it comes before 7/9/.... Is there a way to change the Calendar to save 7/9/2007 as 07/09/2007? Or what would you recommend?
Thanks a bunch for your help
-Rich

I never use the calendar control, so not sure if there is a way to take the data and format it properly as it is being stored. Well, I know you can do that, but it seems like a kludge.

Are you sure that you are not using a text field to store the data in? That is more likely the cause than the control. The date field should be should be Date/Time, and not Text. (or even number?)

Check that first and let me know.


mmcdonal

What I have found is that the formatting of the date really drives the sorting on the form. I have figured out my own way of having it be reliable, but still look normal to the user.

Here is what I do in this case:

I format the data of the underlying query like this:
Format$([ProductionDate],"yyyy/mm/dd") AS PdateSortsGood

So, the info comes out like 2007/07/25.
But, since the users of the form don't want to see this strange format which make it line up down the form correctly, I reformat in the text box the way the user wants to see it.

Then, in the control source area of your text box properties put it:
=Format$([PdateSortsGood],"mm/dd/yyyy") which will then make it appear common to the user.

Here is an example of where is used it in my where clause with the use of calendar dates

Format$(DateAdd("d", 1, Forms![Pop_ProPrinting]!CalendarEnd), "yyyy/mm/dd")

I always do my underneath formatting with yyyy/mm/dd, yet show the user what is common (ie, mm/dd/yyyy, or "ShortDate"

Coby