Time stored as varchar

[Jim]

I have time values that are stored in sql server as a varchar tha look like
this:
10:00:00 AM or 9:00:00 AM

I got the sort to work on am and pm but when I run this query in access
against the sql server table:

SELECT time from schedule
where time is not null
order by right(time, 2), mid(time,1,5);

it starts at 10:00:00 AM not 9:00:00 AM

How can I get the times to sort right?

 

[Jeff Boyce]

Jim

Characters are ... characters. If you aren't working with numbers (things
you add/subtract), the sort is working just the way you told it to ("1xxxx"
comes before "9xxxx").

Good luck

Jeff Boyce
<Access MVP>

 

[John Vinson]

I have time values that are stored in sql server as a varchar tha look like
this:
10:00:00 AM or 9:00:00 AM

I got the sort to work on am and pm but when I run this query in access
against the sql server table:

SELECT time from schedule
where time is not null
order by right(time, 2), mid(time,1,5);

it starts at 10:00:00 AM not 9:00:00 AM

How can I get the times to sort right?

Try

Order By CDate([time])

to convert the time to a Date/Time field (which will sort
chronologically).

John W. Vinson[MVP]

 

[Jim]

That did it...thanks!!!!!