Sort by Time in Ascending order

[MelissaT]

I have a query that pulls data from three tables. Table1 contains all clients. Then each client has a record in either Table2 or Table3, but not both. I need to pull a time field from either Table2 or Table3, which ever one they have a record in, and be able to display this on a form in one text box. In my query, I created an expression:

ArrivalTime = Format(IIf(IsNull([Table2.ArriveTime]),[Table3.ArriveTime],[Table2.ArriveTime]),"Medium Time")

Then on my form I just have a text box that shows ArrivalTime. This pulls the correct time for each record.

However, when I view my form, I want to see the records in ascending order of ArrivalTime. Even though I formatted my Expression of ArrivalTime as medium time, 01:00PM still shows before 09:30AM.

Any ideas?

Melissa
Designing Access databases since 1999

 

[PHV]

Don't use Format in the query and set the display Format property of the textbox instead:
ArrivalTime = Nz(Table2.ArriveTime,Table3.ArriveTime)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886