Date compare and put priority

[dand_dd]

Hi,
I have to compare to date fields and as result i have to write into another field a priority.
I have 2 example queries but works wrong.

When F9 is less than Time1 then prio=1, When F9 is between Time1 and Time2( F9 is greater than Time1 but less than Time2) then prio=2, When F9 is between Time2 and Time3( F9 is greater than Time2 but less than Time3) then prio=3, when F9 is greater than Time3 then prio=4.

For example:

Time1 --- Time2 --- Time3 --prio -- F9
-------------------------------------------------------------
29/02/08 -- 07/03/08 -- 01/05/08-- 1 -- 28/02/08
29/02/08 -- 07/03/08 -- 01/05/08-- 2 -- 30/02/08
29/02/08 -- 07/03/08 -- 01/05/08-- 3 -- 20/03/08
29/02/08 -- 07/03/08 -- 01/05/08-- 4 -- 01/06/08

[Oorang]

I see two possible issues. First, this:
UPDATE copie SET copie.prio = Switch([F9]<[time1],1,[F9]>[time2],2,[F9]<[time1],3,[F9]>=[time3],4);Should be this:
UPDATE copie SET copie.prio = Switch([F9]<[time1],1,[F9]<[time2],2,[F9]<[time3],3,[F9]>=[time3],4);
Also as a side note, a possible "gotcha" can be how TimeX is interpreted. Since it is a text field, when you use a math operator on it, an implicit conversion is performed. If you have your regional and date settings set for the D/m/yy format, no worries, but if you (like me) are set for US dates m/d/yy then 07/03/08 converts to July, 3rd 2008. For my test on your database I used a user defined function to force the proper conversion: Public Function GetUKDate(ByVal value As String, Optional ByVal delimiter As String = "/") As Date
Dim strSegments() As String
strSegments = Split(value, delimiter)
GetUKDate = DateSerial(strSegments(2), strSegments(1), strSegments(0))
End Function
Then nested the date values in the function:
Switch(GetUKDate([F9])<GetUKDate([time1]),1,GetUKDate([F9])<GetUKDate([time2]),2,GetUKDate([F9])<GetUKDate([time3]),3,GetUKDate([F9])>=GetUKDate([time3]),4)

[dand_dd]

Thanks. I will try.