Help with IIf/Switch expression

[lookforsmt]

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

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]

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.

 

[lookforsmt]

Thanks DBguy for the suggestion. I would opt for custom function, how can i write one.

 

[theDBguy]

Thanks DBguy for the suggestion. I would opt for custom function, how can i write one.

Hi. You could start with something like:

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...

 

[lookforsmt]

Thanks DBGuy, will give a try.

 

[theDBguy]

Thanks DBGuy, will give a try.

Hi. You're welcome. Let us know how it goes. Good luck!

 

[lookforsmt]

HI! i am not getting correct result. Below is the code. Is this correct way or something more to this.

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]

HI! i am not getting correct result. Below is the code. Is this correct way or something more to this.

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]

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...

Okay, please try this one...

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...

 

[CJ_London]

you could use the datediff function

something like

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

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

 

[lookforsmt]

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

 

[theDBguy]

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

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:

NewBusinessDate: IIf([CaptureTime]>#10:00:01#,[BusinessDate]+1,[BusinessDate])

 

[lookforsmt]

HI! DBGuy, sorry for the confusion and incomplete information. Actually it pertains to the same code

i have table: tblTemp

SrNo                     AutoNumber
ScanDate                 Date
Source                   Text
CaptureTime              Text
NewTime                  Text
BusinessDate             Date

i have tried your 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.

 

[lookforsmt]

Pls have a look at the DB.

 

[theDBguy]

Pls have a look at the DB.

Hi. If your [CaptureTime] field is Text, then we're using the wrong delimiter. Try it this way:

Expr1: IIf([CaptureTime]>"10:00:01",[ScanDate]+1,[ScanDate])

 

[lookforsmt]

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.

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])

 

[lookforsmt]

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]

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.

Hi. Glad to hear you got it sorted out. Good luck with your project.