sort text field that contains numbers and characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
ktm400 said:
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
Click to expand...

It is likely shorting like

10/10/2005
11/10/2004
11/10/2005
2/10/2005

In other words it is sorting by the first "character" not by the date.
The reason is likely because the field type is text and not date so it does
not know that 10/10/2005 is a date.

If you want to sort by date, you need the filed type to be date. The
same goes for numbers.
 
I cannot change the column data type... I have to use what I have, it is a
linked obdc table
 
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]
 
ktm400 said:
I cannot change the column data type... I have to use what I have, it
is a linked obdc table
Click to expand...

I would just import it to a local table and change it there, assuming it
is a one time thing.

You should also be able to create an array and convert that to date via
code or maybe reformat it to

YYYY/MM/DD still in text to get the order you want. BTW make that
2005/10/02 not 2005/10/2.
 
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.
 
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...
 
Back
Top