Sorting date strings

  • Thread starter Thread starter Dave Cullen
  • Start date Start date
D

Dave Cullen

I have a text field in one of my tables that's a string reresentation of
a date. When I sort on this field, it doesn't recognize that
"06/12/2002" is earlier than "06/06/2003", and if I try to make a query
to list records over a date range it doesn't work.

I CAN'T change the data type of the field to Date. It's a linked Oracle
table and I can't change its design. Is there any way to get Access to
correctly sort this column?

Thanks.
 
Create a select query and conver the TEXT DATE field to a
DATE field by using "cdate" function.
 
A clunky but effective method I use is to break apart the
date into year, month, and day fields then sort by year,
month, and day.

Or, you could query your Oracle table and add a field that
changes the datatype and sorts on it.

Select *, CDATE(fieldname)as newdate from oracletable
Orderby newdate;

hth
Sasha
 
Dear Dave:

You CAN, however, convert the value to a DateTime value within your
query, and for purposes of all query work. Simply use:

CDATE(YourColumnName)

as as new column in the query. You can then sort by this, and it will
be in proper date order.

If you are going to do extensive query work on this data, you might
want to write a query that simply presents the columns of the Oracle
table, with this particular column converted. Then, base all
additional queries on that one rather than accessing the table
directly. Then, you'll never have this problem again.

The down side of all this is, there isn't, and cannot be an index on
this column within Oracle that puts them in date order. So, every
time you want to access this data in a way that could benefit in
performance by a date index, you're out of luck. The ordering must be
build every time at run-time, and most date related filtering will
result in a table scan. However, this is the result of bad design on
the Oracle side. If this becomes a big problem, you might want to
import the data from Oracle periodically and build the table more
efficiently in Access, then access that copy of the table.

I have a text field in one of my tables that's a string reresentation of
a date. When I sort on this field, it doesn't recognize that
"06/12/2002" is earlier than "06/06/2003", and if I try to make a query
to list records over a date range it doesn't work.

I CAN'T change the data type of the field to Date. It's a linked Oracle
table and I can't change its design. Is there any way to get Access to
correctly sort this column?

Thanks.
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks Tom, that worked. I created a query column:

DATE:CDate(SESSIONDATE)

At least the column is sorted now. The problem now is that I don't know
how to use relational operators for the criteria. If I use something
like >8/17/2003 it shows me all dates. Is there a secial way to list
dates as values in criteria?

Thanks
 
Back
Top