SWITCH problem in time field

[Bratlien]

I have a query with data grouped by time, with the goal being to combine values for each 5-minute time interval over the course of several months into a single 24-hour period (resulting in 288 records). Most of the time values, when converted to decimals, are between 0 and 1. However, some of the times are actually greater than 1. They still display correctly in time format, but are unable to group with the rest of the times since they have different values.

I need to construct a statement to
a.) Keep the given value if SensorTime<=1
b.) If SensorTime>1, subtract an integer value (between 1 and 4) so it is between 0 and 1 and will then group with the rest of the times

I experimented with a SWITCH function but I'm not sure how to specify "1<SensorTime<=2." I think the current language will subtract up to 10 from numbers less than 1 (-1 if less than 2, -2 if less than 3, etc.). Here's what I have so far:

SELECT Sensor3.SensorDate, Switch(Sensor3.SensorTime<=1,Sensor3.SensorTime,Sensor3.SensorTime<=2,(Sensor3.SensorTime-1),Sensor3.SensorTime<=3,(Sensor3.SensorTime-2),Sensor3.SensorTime<=4,(Sensor3.SensorTime-3),Sensor3.SensorTime<=5,(Sensor3.SensorTime-4)) AS Expr1, Sensor3.SensorTime, Sensor3.Volume, Sensor3.Trucks
FROM Sensor3
WHERE (((Sensor3.LaneName)="NB1" Or (Sensor3.LaneName)="NB2" Or (Sensor3.LaneName)="NB3"));

Can anybody suggest a way to subtract the correct integer value from each time to get a number between 0 and 1?

 

[chergh]

Try:

sensor3.sensortime - int(sensor3.sensortime)

 

[Guus2005]

Switch expects a boolean as its first argument so

answer: nz(Switch([Account]>50000001 And [Account]<= 53000000;"Yes");"No")

is valid

Enjoy!

 

[Bratlien]

Do you mean replacing the [1,2,3,4] values with int(Sensor3.SensorTime)? If so, that didn't seem to work.

 

[Bratlien]

Guus2005:

Not sure I'm clear on what you mean, but I did modify the argument slightly:

SELECT Sensor3.SensorDate, Switch((Sensor3.SensorTime<=1),(Sensor3.SensorTime),(Sensor3.SensorTime<=2 AND Sensor3.SensorTime>1),(Sensor3.SensorTime-1),(Sensor3.SensorTime<=3 AND Sensor3.SensorTime>2),(Sensor3.SensorTime-2),(Sensor3.SensorTime<=4 AND Sensor3.SensorTime>3),(Sensor3.SensorTime-3),(Sensor3.SensorTime<=5 AND Sensor3.SensorTime>4),(Sensor3.SensorTime-4)) AS Expr1, Sensor3.SensorTime, Sensor3.Volume, Sensor3.Trucks
FROM Sensor3
WHERE (((Sensor3.LaneName)="NB1" Or (Sensor3.LaneName)="NB2" Or (Sensor3.LaneName)="NB3"));

Still getting time values greater than 1 though.

 

[chergh]

I mean:

SELECT Sensor3.SensorDate, sensor3.SensorTime - int(sensor3.Sensortime) as Expr01, Sensor3.SensorTime, Sensor3.Volume, Sensor3.Trucks
FROM Sensor3
WHERE (((Sensor3.LaneName)="NB1" Or (Sensor3.LaneName)="NB2" Or (Sensor3.LaneName)="NB3"));

 

[Bratlien]

How would I use a boolean in this case? Or should I resort to a large nested IIF statement?

 

[Bratlien]

chergh:

That one didn't work either. Thanks though for clarifying.

 

[chergh]

Try:

SELECT Sensor3.SensorDate, CLng(sensor3.SensorTime) - int(CLng(sensor3.Sensortime)) as Expr01, Sensor3.SensorTime, Sensor3.Volume, Sensor3.Trucks
FROM Sensor3
WHERE (((Sensor3.LaneName)="NB1" Or (Sensor3.LaneName)="NB2" Or (Sensor3.LaneName)="NB3"));

 

[Bratlien]

That one zeroed the entire SensorTime field.

[EDIT]
My mistake, it added a new field "Expr01" of all zeros. The time issue still remains though.

 

[chergh]

My bad, didnt notice CLng rounded. This one should work:

SELECT Sensor3.SensorDate, CDbl(sensor3.SensorTime) - int(CDbl(sensor3.Sensortime)) as Expr01, Sensor3.SensorTime, Sensor3.Volume, Sensor3.Trucks
FROM Sensor3
WHERE (((Sensor3.LaneName)="NB1" Or (Sensor3.LaneName)="NB2" Or (Sensor3.LaneName)="NB3"));

 

[Bratlien]

chergh:

That appears to have done the trick with the values greater than 1. Thanks! I'm still having an issue with the times grouping together but now I suspect it's another matter altogether.