Return an integer based on time.

[djsmith2005]

I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.

I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to

Function SortStart(StartTime As String) As Integer

then I get zero.

I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.

Sort_Start: IIf([StartTime]="7:00 AM",1,IIf([StartTime]="8:00 AM",2,IIf([StartTime]="8:45 AM",3,IIf([StartTime]="9:00 AM",4,IIf([StartTime]="9:15 AM",5,IIf([StartTime]="10:00 AM",6,IIf([StartTime]="10:15 AM",7,IIf([StartTime]="10:30 AM",8,IIf([StartTime]="12:00 PM",9,IIf([StartTime]="1:30 PM",10,IIf([StartTime]="1:45 PM",11,IIf([StartTime]="2:00 PM",12,IIf([StartTime]="3:00 PM",13,IIf([StartTime]="4:00 PM",14))))))))))))))

Does anyone have any ideas, I need to finish this report for tomorrow and appreciate any help I can get.

Thank you.

 

[pbaldy]

Hint:

SortStart
Sort_Start

 

[djsmith2005]

OMG I thought when I had tried that before, I had gotten a circular reference message.

Thank you, Thank you Thank you.

 

[pbaldy]

Happy to help!