TimeValue 1

[kvest]

Hello,
I have been working on the following query to determine what shift different incidents occur on. What I am getting is a 1 on some incidents and nothing on the others. The one is appearing on the wrong incidents.

The field Inc Time is a military time field (24 hour clock) is is currently a text field. I have tried changing it to a date/time field, but get the same results.

Below is my query...

SELECT [tbl Incident Approval].Incident_Number, [tbl Incident Approval].Incident_Date, [tbl Incident Approval].[Inc Time], [tbl Incident Approval].[Day of Week], Switch([Inc Time]>=TimeValue("00:00:01") And [Inc Time]<=TimeValue("07:00:00"),1,[Inc Time]>=TimeValue("07:00:01") And [Inc Time]<=("16:00:00"),2,[Inc Time]>=TimeValue("16:00:01") And [Inc Time]<=TimeValue("23:59:59"),3) AS Shift
FROM [tbl Incident Approval];

What am I missing? An assistance would be great.

 

[SkipVought]

If [Inc Time] is a string, then IT must be converted using TimeValue.

Also you are not consistent converting your literal time strings.

...And [Inc Time]<=("16:00:00")...

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[kvest]

Thanks...I missed that. It is working great.

For anyone in the future viewing this....here is the working code

Switch([Inc Time] >= TimeValue("00:00") And [Inc Time] <= TimeValue("07:00"),1,([Inc Time] >= TimeValue("07:00:01") And [Inc Time] <= TimeValue("16:00:00"),2,([Inc Time] >= TimeValue("16:00:01") And [Inc Time] <= TimeValue("23:59:59"),3) AS Shift