lookforsmt
Registered User.
- Local time
- Today, 06:58
- Joined
- Dec 26, 2011
- Messages
- 672
HI! All
I have IIf statement in query which is working fine for 13 columns to insert the NewTime data in tblTemp.
But when i add the 14th IIf statment i get below error.
"Expresssion too complex in query expression......"
Is there a restriction on IIf/Switch expression.
Below is my query which is working fine for 13 columns
Is there a alternate way to write the IIf statement in vba code. I have a table called tblRange with the time range where it looks in this table for the time range and insert in tblTemp
Any suggestions
I have IIf statement in query which is working fine for 13 columns to insert the NewTime data in tblTemp.
But when i add the 14th IIf statment i get below error.
"Expresssion too complex in query expression......"
Is there a restriction on IIf/Switch expression.
Below is my query which is working fine for 13 columns
Code:
UPDATE tblTemp SET NewTime = IIf([CaptureTime] >= '00:00:01' and [CaptureTime] <='07:00:00', '24:00-07:00',
IIf([CaptureTime] >= '07:00:01' and [CaptureTime] <='08:00:00', '07:00-08:00',
IIf([CaptureTime] >= '08:00:01' and [CaptureTime] <='09:00:00', '08:00-09:00',
IIf([CaptureTime] >= '09:00:01' and [CaptureTime] <='09:30:00', '09:00-09:30',
IIf([CaptureTime] >= '09:30:01' and [CaptureTime] <='10:00:00', '09:30-10:00',
IIf([CaptureTime] >= '10:00:01' and [CaptureTime] <='10:30:00', '10:00-10:30',
IIf([CaptureTime] >= '10:30:01' and [CaptureTime] <='11:00:00', '10:30-11:00',
IIf([CaptureTime] >= '11:00:01' and [CaptureTime] <='12:00:00', '11:00-12:00',
IIf([CaptureTime] >= '12:00:01' and [CaptureTime] <='13:00:00', '12:00-13:00',
IIf([CaptureTime] >= '13:00:01' and [CaptureTime] <='14:00:00', '13:00-14:00',
IIf([CaptureTime] >= '14:00:01' and [CaptureTime] <='15:00:00', '14:00-15:00',
IIf([CaptureTime] >= '15:00:01' and [CaptureTime] <='16:00:00', '15:00-16:00',
IIf([CaptureTime] >= '16:00:01' and [CaptureTime] <='24:00:00', '16:00-24:00',
)))))))))))));
Is there a alternate way to write the IIf statement in vba code. I have a table called tblRange with the time range where it looks in this table for the time range and insert in tblTemp
Any suggestions