Converted text to date not sorting

[RichO]

Hello all,

I have a text field that contains a date in this format

3/11/2011 11:32:17 AM

I want to be able to keep just the date, not the time, and sort it. I thought I knew how to do this but apparently not.

I have tried every combination of CDate, DateValue, and Format$ and I am not getting the results. It is still sorting by string value, not date value.

Thanks for your help.

 

[boblarson]

In your query you should be able to use

CDate on a field and then sort by that created field. Are you sure you don't have any fields sorting before that converted field?

 

[RichO]

This is one of the ways I tried it and the result. Notice the 2012's mixed in there? I also tried flipping Format$ and CDate in the other order and the results were even worse.

 

[boblarson]

the Format actually turns it to text, which I'm guessing you didn't know.

But how about trying

DateValue([FieldName])

 

[RichO]

No I did not know that Format$ converted a date into text.

DateValue did the trick. Thanks much!

 

[missinglinq]

...did not know that Format$ converted a date into text...

FYI, Format() converts everything into Text, regardless of the original Datatype.

Linq ;0)>

 

[gemma-the-husky]

why do you need to sort the formatted string anyway? can you not just sort the date as it is stored in the table?

 

[Brianwarnock]

I don't think a text date field would sort correctly

Brian

 

[Pat Hartman]

There is no reason to use Format() or even DateValue(). If the field is a date datatype, it will sort correctly even if it also contains time. The only time you would need to sort by date only is if there is a field following date in the sort order. For example, if you wanted to sort by TransactionDate and customer, you would need to use DateValue() to extract the date part.

 

[RichO]

The field is a text data type. It would make the most sense to be a date type but on occasion that field will contain non-date data but I use IsDate to filter those out when sorting in a query.