Query Problem

[Novice1]

I have a table with the customer sign-in time [TimeIn]. I have another field with a drop down selection of appointment times (e.g., N/A, 7:00, 7:15, 7:30, 7:45, etc.).

In my query I want to sequence the records based on appointment time then [TimeIn]. If there isn't an appointment time (e.g., N/A), then I want to sort based on [TimeIn].

I created this field: CombinedTime: IIf([AppointmentTime]<>"N/A",[AppointmentTime],[TimeIn])

But it doesn't sort as I expected. Is the problem related to using a short text field for the appointment time? I could change the field type (short time) but I want the field to default to "N/A" since most of my customers don't have an appointment.

Any help would be appreciated.

 

[pbaldy]

Yes, appointment time being a text field is causing you to get an alphabetic sort. You can try wrapping it in the CDate() function in your formula.

 

[Novice1]

Thank you. That did the trick. The new construct works:

CombinedTime: IIf([AppointmentTime]<>"N/A",CDate([AppointmentTime]),[TimeIn])

 

[pbaldy]

No problem.