sort text field that contains numbers and characters

K

ktm400

Iam trying to sort a text column that has a date value ie 7/10/2005.
It does not work to simply sort descending or ascending.....
How can I do this?
Thanks for any help
 
K

ktm400

I cannot change the column data type... I have to use what I have, it is a
linked obdc table
 
J

John Vinson

I cannot change the column data type... I have to use what I have, it is a
linked obdc table
Click to expand...

If you want it to sort chronologically, put in a calculated field

CDate([textfield])

or, if the textfield might be null,

CDate(NZ([textfield], "1/1/100"))

and sort by it.

John W. Vinson[MVP]
 
D

Douglas J Steele

Assuming your regional settings have your short date format set to
mm/dd/yyyy, you should be able to use the CDate function to convert the text
to a proper date. (NOTE: I seldom recommend this approach, as it will not
work for users who have their short date format set to dd/mm/yyyy, and
therefore is not a generic approach!)

Since you can guarantee the format of the text date, though, another option
would be to write a function that parses the date into its component parts,
and uses the DateSerial function to convert to a date.

You can use either of these methods on a query based on your ODBC table, and
then use that query wherever you would otherwise have used the table.
 
K

ktm400

Thanks for all the help!

Douglas J Steele said:
Assuming your regional settings have your short date format set to
mm/dd/yyyy, you should be able to use the CDate function to convert the text
to a proper date. (NOTE: I seldom recommend this approach, as it will not
work for users who have their short date format set to dd/mm/yyyy, and
therefore is not a generic approach!)

Since you can guarantee the format of the text date, though, another option
would be to write a function that parses the date into its component parts,
and uses the DateSerial function to convert to a date.

You can use either of these methods on a query based on your ODBC table, and
then use that query wherever you would otherwise have used the table.
Click to expand...
 
Top