Can I sort by Shift?

[SteveGr]

Happy New Year,

I have a query that I would like to display by shift. (Ex 3,1,2) in that order because 3rd shift starts a new calender day.

I do not calculate time, just date and shift. So in the query to get 3rd shift to show up on a particular date before 1st shift is puzzling me.

Any suggestions?
Thanks, Stevegr

 

[ColinEssex]

You could use a "ShiftSort" table. Make a new table, 2 fields, "shift" and "shiftOrder"

So its like this

Shift................ShiftOrder

3........................1

1........................2

2........................3


Then put this in the query, link by shift and use the ShiftOrder field to sort on. I reckon that might work.

Col

 

[ddmcn]

Sequence Field

Steve,

Try using an additional field called 'shiftsequence' and assign the first shift of the day the sequence number 1...the second, number 2 and so on. Then sort on the sequence number rather than the shift number.

 

[ddmcn]

Great minds think alike, I think

Colin put it more succinctly than I, but you get the idea, right?

 

[ColinEssex]

Sorry Den.......just beat you to it I think...

 

[SteveGr]

Thanks, That was simple

 

[Fornatian]

You could avoid a third table using the Choose() function as a calculated field in the query.

Using a field header like:

ShiftSort: Choose([Shift],2,3,1)

would return the required order.

 

[Rich]

Choose, Switch and Iif have to evaluate all the conditions before returning the correct one, so in this case, isn't the third table option more efficient and manageable?

 

[Fornatian]

Forgot about that, yep, you're right Rich. Don't get me wrong I liked the third table idea, just pushing alternatives for consideration.

 

[raskew]

Rich-

Agree with your arguments re Iif() and Choose() but, last time I looked, the Switch() function worked from left to right till it hit on the first expression that evaluated to True--same as a Select Case statement.

Am I missing something?

Best wishes,

Bob

 

[Rich]

Yes I agree Bob it's confusing, this comes from the 97 help on the function

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch
returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

· None of the expressions is True.
· The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.