help with date query in access

[E_Gold]

hi

i have field `Tdate` (text type) on my Table `MyTbl`

i need query that sort by date, i try this:

select * from MyTbl order by Tdate

**but because Tdate is Text i get wrong results**

how to fix it ? is there any convert to date in access ?

thanks in advance

 

[Mr. B]

You need to change the format of the field from text to a date/time field. Then you will be able to use it correctly.

 

[vbaInet]

Or better still change the data type of that field in the table to Date/Time.

 

[Mr. B]

vbaInet,

Sorry that I was not clear about what I was trying to say. You said exactly what I meant.

Thanks for clearinig that up.

 

[Brianwarnock]

To actually answer your question I find the best way to convert a Text Date to a correct format is to use DateSerial the help for this appears in VBA help but the format is

DateSerial(Year,Month,Day)
so assuming English dates dd/mm/yyyy
TDATE 21012011 should be 21/01/2011

Newfield: DateSerial(Right(Tdate,4),Mid(Tdate,3,2),Left(Tdate,2))

Hope this helps

Brian

 

[vbaInet]

vbaInet,

Sorry that I was not clear about what I was trying to say. You said exactly what I meant.

Thanks for clearinig that up.

Ah, I thought you were referring to Formatting the field