Help with IIf/Switch expression (1 Viewer)

L

lookforsmt

Registered User.
Local time
Today, 08:05
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
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
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:05
Joined
Oct 29, 2018
Messages
21,467
Hi. Yes, I think IIf() has a limit (14) in how many levels you can nest them. An alternative is to create a table with your criteria/conditions and simply join it in your query. Either that or use a custom function.
 
L

lookforsmt

Registered User.
Local time
Today, 08:05
Joined
Dec 26, 2011
Messages
672
Thanks DBguy for the suggestion. I would opt for custom function, how can i write one.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:05
Joined
Oct 29, 2018
Messages
21,467
lookforsmt said:
Thanks DBguy for the suggestion. I would opt for custom function, how can i write one.
Click to expand...
Hi. You could start with something like:
Code: 
Public Function GetNewTime(CaptureTime As Date) As String
Dim strTimeRange As String


Select Case Hour(CaptureTime-#0:00:01#)
    Case 0 To 6
        strTimeRange = "24:00-07:00"
    Case 7
        strTimeRange = "07:00-08:00"
'and so on...

End Select


GetNewTime = strTimeRange


 End Function
Hope it helps...
 
L

lookforsmt

Registered User.
Local time
Today, 08:05
Joined
Dec 26, 2011
Messages
672
HI! i am not getting correct result. Below is the code. Is this correct way or something more to this.
Code: 
Public Function GetNewTime(CaptureTime As Date) As String
Dim strTimeRange As String

Select Case Hour(CaptureTime - #12:00:01 AM#)
    Case 0 To 6
        strTimeRange = "24:00-07:00"
    Case 7
        strTimeRange = "07:00-08:00"
    Case 8
        strTimeRange = "08:00-09:00"
    Case 9
        strTimeRange = "09:00-09:30"
    Case 10
        strTimeRange = "09:30-10:00"
    Case 11
        strTimeRange = "10:00-10:30"
    Case 12
        strTimeRange = "10:30-11:00"
    Case 13
        strTimeRange = "11:00-12:00"
    Case 14
        strTimeRange = "12:00-13:00"
    Case 15
        strTimeRange = "13:00-14:00"
    Case 16
        strTimeRange = "14:00-15:00"
    Case 17
        strTimeRange = "15:00-16:00"
    Case 18
        strTimeRange = "16:00-17:00"
    Case 19
        strTimeRange = "17:00-18:00"
    Case 20
        strTimeRange = "18:00-24:00"
       
End Select

    GetNewTime = strTimeRange

End Function
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:05
Joined
Oct 29, 2018
Messages
21,467
lookforsmt said:
HI! i am not getting correct result. Below is the code. Is this correct way or something more to this.
Click to expand...
Oh, sorry, I didn't see you were doing half-hours between 8 and 11. Let me see if I can fix it. Stand by...
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:05
Joined
Oct 29, 2018
Messages
21,467
theDBguy said:
Oh, sorry, I didn't see you were doing half-hours between 8 and 11. Let me see if I can fix it. Stand by...
Click to expand...
Okay, please try this one...
Code: 
Public Function GetNewTime(CaptureTime As Date) As String
Dim strTimeRange As String

Select Case Hour(CaptureTime - #12:00:01 AM#)
    Case 0 To 6
        strTimeRange = "24:00-07:00"
    Case 7
        strTimeRange = "07:00-08:00"
    Case 8
        strTimeRange = "08:00-09:00"
    Case 9
        If TimeValue(CaptureTime) < #9:30:00 AM# Then
            strTimeRange = "09:00-09:30"
        Else
            strTimeRange = "09:30-10:00"
        End If
    Case 10
        If TimeValue(CaptureTime) < #10:30:00 AM# Then
            strTimeRange = "10:00-10:30"
        Else
            strTimeRange = "10:30-11:00"
        End If
    Case 11
        strTimeRange = "11:00-12:00"
    Case 12
        strTimeRange = "12:00-13:00"
    Case 13
        strTimeRange = "13:00-14:00"
    Case 14
        strTimeRange = "14:00-15:00"
    Case 15
        strTimeRange = "15:00-16:00"
    Case 16
        strTimeRange = "16:00-17:00"
    Case 17
        strTimeRange = "17:00-18:00"
    Case Else
        strTimeRange = "18:00-24:00"
End Select

GetNewTime = strTimeRange

End Function
Hope it's close enough...
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 05:05
Joined
Feb 19, 2013
Messages
16,605
you could use the datediff function

something like

Code: 
switch(datediff("h",#00:00:00#,CaptureTime)<=7,"24:00-07:00",datediff("h",#00:00:00#,CaptureTime)>=16,"16:00-24:00",true,format(datediff("h",#00:00:00#,CaptureTime),"00") & ":00-" & format(datediff("h",#00:00:00#,CaptureTime)+1,"00") & ":00")

or use the partition function

Code: 
replace(replace(partition(datediff("h",#00:00:00#,CaptureTime),8,16,1),":",":00-") & ":00","  ","24")

Edit: just seen the issue re 1/2 hours. Missed it before. partition function won't work in that case, at least not with more work, but the switch function can be extended in much the same way DBG is suggesting
 
L

lookforsmt

Registered User.
Local time
Today, 08:05
Joined
Dec 26, 2011
Messages
672
Thanks DBGuy & CJ_London both the solutions have worked for me.
The query provided by CJ_London is giving me the same result as DBGuy

I wanted to add the below criteria to the vba code.

In table, "tblTemp" has fields "BusinessDate" and "Source"

If the SourceA CaptureTime after 10:00:01 then the BusinessDate should be next date, but if next date is Sunday then the date should be Monday's date.
Where will i add this in the vba code.

Thanks
 
Last edited:
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:05
Joined
Oct 29, 2018
Messages
21,467
lookforsmt said:
Thanks DBGuy & CJ_London both the solutions have worked for me.
The query provided by CJ_London is giving me the same result as DBGuy

I wanted to add the below criteria to the vba code.

In table, "tblTemp" has fields "BusinessDate" and "Source"

If the SourceA CaptureTime after 10:00:01 then the BusinessDate should be next date, but if next date is Sunday then the date should be Monday's date.
Where will i add this in the vba code.

Thanks
Click to expand...
Hi. Either I am not understand your last question or I'm confused, but it sounds like this last request is unrelated to the code we have provided so far. If so, you may need a separate/new expression/function for it. For instance:
Code: 
NewBusinessDate: IIf([CaptureTime]>#10:00:01#,[BusinessDate]+1,[BusinessDate])
 
L

lookforsmt

Registered User.
Local time
Today, 08:05
Joined
Dec 26, 2011
Messages
672
HI! DBGuy, sorry for the confusion and incomplete information. Actually it pertains to the same code

i have table: tblTemp
Code: 
SrNo                     AutoNumber
ScanDate                 Date
Source                   Text
CaptureTime              Text
NewTime                  Text
BusinessDate             Date
i have tried your code
Code: 
NewBusinessDate: IIf([CaptureTime]>#10:00:01#,[BusinessDate]+1,[BusinessDate])

it changes the Businessdate for all the Source. I need to change the Business date only for SourceA and keep the same ScanDate if its less than 10:00:01

If i am not clear, let me post the DB to make it clear.
 
L

lookforsmt

Registered User.
Local time
Today, 08:05
Joined
Dec 26, 2011
Messages
672
Pls have a look at the DB.
 

Attachments

  • tblSource_1.mdb
    316 KB · Views: 93
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:05
Joined
Oct 29, 2018
Messages
21,467
lookforsmt said:
Pls have a look at the DB.
Click to expand...
Hi. If your [CaptureTime] field is Text, then we're using the wrong delimiter. Try it this way:
Code: 
Expr1: IIf([CaptureTime]>"10:00:01",[ScanDate]+1,[ScanDate])
 
L

lookforsmt

Registered User.
Local time
Today, 08:05
Joined
Dec 26, 2011
Messages
672
Thanks for the patience and time,

I can see it changes the date when the CaptureTime is >10:00:01 but it changes all the other source (A to G). I only wanted SourceA to change when the time is >10:00:01.
Do i need to mention SourceA somewhere in the below code that you provided.
PHP: 
Hi. If your [CaptureTime] field is Text, then we're using the wrong delimiter. Try it this way:
Code:
Expr1: IIf([CaptureTime]>"10:00:01",[ScanDate]+1,[ScanDate])
 
L

lookforsmt

Registered User.
Local time
Today, 08:05
Joined
Dec 26, 2011
Messages
672
Thanks DBGuy, figured out the below which changes the ScanDate for only SourceA

NBDt: IIf([CaptureTime]>"10:00:01" And [Source]="SourceA",[ScanDate]+1,[ScanDate])

Will close the thread for now.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:05
Joined
Oct 29, 2018
Messages
21,467
lookforsmt said:
Thanks DBGuy, figured out the below which changes the ScanDate for only SourceA

NBDt: IIf([CaptureTime]>"10:00:01" And [Source]="SourceA",[ScanDate]+1,[ScanDate])

Will close the thread for now.
Click to expand...
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
You must log in or register to reply here.

Similar threads

B
Overtime Formula
Replies
3
Views
451
BowDan
B
MattBaldry
Solved Sum Total Hours Over 24
Replies
8
Views
461
MattBaldry
MattBaldry
K
Solved Working with Date/time.
Replies
1
Views
594
KitaYama
K
A
Solved How to change date portion at beginning
Replies
13
Views
883
Ashfaque
A
S
ChildCarePlan
Replies
14
Views
948
silentwolf
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom