epxression

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm building the following expression:

after5: IIf([TimeAssigned] Like "6:*PM",[CustExp2]+1,(IIf([TimeAssigned]
Like "7:*PM",[CustExp2]+2,(IIf([TimeAssigned] Like
"8:*PM",[CustExp2]+3,(IIf([TimeAssigned] Like
"9:*PM",[CustExp2]+4,(IIf([TimeAssigned] Like
"10:*PM",[CustExp2]+5,(IIf([TimeAssigned] Like
"11:*PM",[CustExp2]+6,(IIf([TimeAssigned] Like
"12:*AM",[CustExp2]-8,(IIf([TimeAssigned] Like
"1:*AM",[CustExp2]-7,(IIf([TimeAssigned] Like
"2:*AM",[CustExp2]-6,[CustExp2])))))))))))))))))

When I continue to add the 3am, 4am, etc. I get an error message that says
it is too complex. What does that mean? How can I fix it?

Thanks
 
Try:

after5: [CustExp2] + Choose(Hour([TimeAssigned]), -8, -7, -6, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 3, 4, 5, 6)

That assumes that no adjustment is intended if TimeAssigned is between 03:00
and 18:59.
 
I'm building the following expression:

after5: IIf([TimeAssigned] Like "6:*PM",[CustExp2]+1,(IIf([TimeAssigned]
Like "7:*PM",[CustExp2]+2,(IIf([TimeAssigned] Like
"8:*PM",[CustExp2]+3,(IIf([TimeAssigned] Like
"9:*PM",[CustExp2]+4,(IIf([TimeAssigned] Like
"10:*PM",[CustExp2]+5,(IIf([TimeAssigned] Like
"11:*PM",[CustExp2]+6,(IIf([TimeAssigned] Like
"12:*AM",[CustExp2]-8,(IIf([TimeAssigned] Like
"1:*AM",[CustExp2]-7,(IIf([TimeAssigned] Like
"2:*AM",[CustExp2]-6,[CustExp2])))))))))))))))))

When I continue to add the 3am, 4am, etc. I get an error message that says
it is too complex. What does that mean? How can I fix it?
Click to expand...

Two suggestions: one would be to use Switch() rather than nested IIF; and the
other - a bit more work but much simpler and more flexible in the long run -
is to use a table.

Switch() takes arguments in pairs; evaluates the pairs left to right; and
returns the second member of the first pair encountered for which the first
element is True. You can also make some considerable simplification by not
repeating CustExp2 in every choice, and treating the time as a datetime value
rather than as a string (if it IS a string you can use CDate() to convert it
to a date/time):

After5: [CustExp2] + Switch(Hour([TimeAssigned])=18, 1,
Hour([TimeAssigned])=19, 2,
Hour([TimeAssigned[) = 20, 3,
<etc>

Or, to allow more flexibility (e.g. half-hour increments) create a three field
table TimeFactors with StartTime, EndTime, and Factor:

#06:00PM# #07:00PM# 1
#07:00PM# #08:00PM# 2

etc.

Join this to your table using a "non equi join":

SELECT <other fields>, [Factor] * [CustExp2]
FROM yourtable INNER JOIN TimeFactors
ON yourtable.[TimeAssigned] >= Timefactors.StartTime
AND yourtable.[TimeAssigned] < Timefactors.EndTime

John W. Vinson [MVP]
 
Thanks the first suggestion (switch) works. But what so I do with the 8 to
17 hour range? I don't need to add any time to these. Do I have to list
every hour?

John W. Vinson said:
I'm building the following expression:

after5: IIf([TimeAssigned] Like "6:*PM",[CustExp2]+1,(IIf([TimeAssigned]
Like "7:*PM",[CustExp2]+2,(IIf([TimeAssigned] Like
"8:*PM",[CustExp2]+3,(IIf([TimeAssigned] Like
"9:*PM",[CustExp2]+4,(IIf([TimeAssigned] Like
"10:*PM",[CustExp2]+5,(IIf([TimeAssigned] Like
"11:*PM",[CustExp2]+6,(IIf([TimeAssigned] Like
"12:*AM",[CustExp2]-8,(IIf([TimeAssigned] Like
"1:*AM",[CustExp2]-7,(IIf([TimeAssigned] Like
"2:*AM",[CustExp2]-6,[CustExp2])))))))))))))))))

When I continue to add the 3am, 4am, etc. I get an error message that says
it is too complex. What does that mean? How can I fix it?
Click to expand...

Two suggestions: one would be to use Switch() rather than nested IIF; and the
other - a bit more work but much simpler and more flexible in the long run -
is to use a table.

Switch() takes arguments in pairs; evaluates the pairs left to right; and
returns the second member of the first pair encountered for which the first
element is True. You can also make some considerable simplification by not
repeating CustExp2 in every choice, and treating the time as a datetime value
rather than as a string (if it IS a string you can use CDate() to convert it
to a date/time):

After5: [CustExp2] + Switch(Hour([TimeAssigned])=18, 1,
Hour([TimeAssigned])=19, 2,
Hour([TimeAssigned[) = 20, 3,
<etc>

Or, to allow more flexibility (e.g. half-hour increments) create a three field
table TimeFactors with StartTime, EndTime, and Factor:

#06:00PM# #07:00PM# 1
#07:00PM# #08:00PM# 2

etc.

Join this to your table using a "non equi join":

SELECT <other fields>, [Factor] * [CustExp2]
FROM yourtable INNER JOIN TimeFactors
ON yourtable.[TimeAssigned] >= Timefactors.StartTime
AND yourtable.[TimeAssigned] < Timefactors.EndTime

John W. Vinson [MVP]
Click to expand...
 
Thanks the first suggestion (switch) works. But what so I do with the 8 to
17 hour range? I don't need to add any time to these. Do I have to list
every hour?
Click to expand...

If you don't, the Join will simply return a NULL - you won't see the record at
all. Or, you could add a timerange including all those times and simply use an
increment of 0.

John W. Vinson [MVP]
 
I get an error message "expression is too complex" when i do this:

After52:
[CustExp2]+Switch(Hour([TimeAssigned])=18,1,Hour([TimeAssigned])=19,2,Hour([TimeAssigned])=20,3,Hour([TimeAssigned])=21,4,Hour([TimeAssigned])=22,5,Hour([TimeAssigned])=23,6,Hour([TimeAssigned])=0,-8,Hour([TimeAssigned])=1,-7,Hour([TimeAssigned])=2,-6,Hour([TimeAssigned])=3,-5,Hour([TimeAssigned])=4,-4,Hour([TimeAssigned])=5,-3,Hour([TimeAssigned])=6,-2,Hour([TimeAssigned])=7,-1,Hour([TimeAssigned])=8,0)

and also when I do this:

After52:
[CustExp2]+Switch(Hour([TimeAssigned])=18,1,Hour([TimeAssigned])=19,2,Hour([TimeAssigned])=20,3,Hour([TimeAssigned])=21,4,Hour([TimeAssigned])=22,5,Hour([TimeAssigned])=23,6,Hour([TimeAssigned])=0,-8,Hour([TimeAssigned])=1,-7,Hour([TimeAssigned])=2,-6,Hour([TimeAssigned])=3,-5,Hour([TimeAssigned])=4,-4,Hour([TimeAssigned])=5,-3,Hour([TimeAssigned])=6,-2,Hour([TimeAssigned])=7,-1,Hour(([TimeAssigned])>=8 and [TimeAssigned]<=17),0)

John W. Vinson said:
I'm building the following expression:

after5: IIf([TimeAssigned] Like "6:*PM",[CustExp2]+1,(IIf([TimeAssigned]
Like "7:*PM",[CustExp2]+2,(IIf([TimeAssigned] Like
"8:*PM",[CustExp2]+3,(IIf([TimeAssigned] Like
"9:*PM",[CustExp2]+4,(IIf([TimeAssigned] Like
"10:*PM",[CustExp2]+5,(IIf([TimeAssigned] Like
"11:*PM",[CustExp2]+6,(IIf([TimeAssigned] Like
"12:*AM",[CustExp2]-8,(IIf([TimeAssigned] Like
"1:*AM",[CustExp2]-7,(IIf([TimeAssigned] Like
"2:*AM",[CustExp2]-6,[CustExp2])))))))))))))))))

When I continue to add the 3am, 4am, etc. I get an error message that says
it is too complex. What does that mean? How can I fix it?
Click to expand...

Two suggestions: one would be to use Switch() rather than nested IIF; and the
other - a bit more work but much simpler and more flexible in the long run -
is to use a table.

Switch() takes arguments in pairs; evaluates the pairs left to right; and
returns the second member of the first pair encountered for which the first
element is True. You can also make some considerable simplification by not
repeating CustExp2 in every choice, and treating the time as a datetime value
rather than as a string (if it IS a string you can use CDate() to convert it
to a date/time):

After5: [CustExp2] + Switch(Hour([TimeAssigned])=18, 1,
Hour([TimeAssigned])=19, 2,
Hour([TimeAssigned[) = 20, 3,
<etc>

Or, to allow more flexibility (e.g. half-hour increments) create a three field
table TimeFactors with StartTime, EndTime, and Factor:

#06:00PM# #07:00PM# 1
#07:00PM# #08:00PM# 2

etc.

Join this to your table using a "non equi join":

SELECT <other fields>, [Factor] * [CustExp2]
FROM yourtable INNER JOIN TimeFactors
ON yourtable.[TimeAssigned] >= Timefactors.StartTime
AND yourtable.[TimeAssigned] < Timefactors.EndTime

John W. Vinson [MVP]
Click to expand...
 
How about:

[CustExp2] + IIf(Hour([TimeAssigned]) <= 8, Hour([TimeAssigned]) - 8,
Hour([TimeAssigned]) - 17)

I don't know what if anything happens when Hour([TimeAssigned]) is from 9 to
17, but maybe this will suggest an approach.

ty said:
I get an error message "expression is too complex" when i do this:

After52:
[CustExp2]+Switch(Hour([TimeAssigned])=18,1,Hour([TimeAssigned])=19,2,Hour([TimeAssigned])=20,3,Hour([TimeAssigned])=21,4,Hour([TimeAssigned])=22,5,Hour([TimeAssigned])=23,6,Hour([TimeAssigned])=0,-8,Hour([TimeAssigned])=1,-7,Hour([TimeAssigned])=2,-6,Hour([TimeAssigned])=3,-5,Hour([TimeAssigned])=4,-4,Hour([TimeAssigned])=5,-3,Hour([TimeAssigned])=6,-2,Hour([TimeAssigned])=7,-1,Hour([TimeAssigned])=8,0)

and also when I do this:

After52:
[CustExp2]+Switch(Hour([TimeAssigned])=18,1,Hour([TimeAssigned])=19,2,Hour([TimeAssigned])=20,3,Hour([TimeAssigned])=21,4,Hour([TimeAssigned])=22,5,Hour([TimeAssigned])=23,6,Hour([TimeAssigned])=0,-8,Hour([TimeAssigned])=1,-7,Hour([TimeAssigned])=2,-6,Hour([TimeAssigned])=3,-5,Hour([TimeAssigned])=4,-4,Hour([TimeAssigned])=5,-3,Hour([TimeAssigned])=6,-2,Hour([TimeAssigned])=7,-1,Hour(([TimeAssigned])>=8
and [TimeAssigned]<=17),0)

John W. Vinson said:
I'm building the following expression:

after5: IIf([TimeAssigned] Like "6:*PM",[CustExp2]+1,(IIf([TimeAssigned]
Like "7:*PM",[CustExp2]+2,(IIf([TimeAssigned] Like
"8:*PM",[CustExp2]+3,(IIf([TimeAssigned] Like
"9:*PM",[CustExp2]+4,(IIf([TimeAssigned] Like
"10:*PM",[CustExp2]+5,(IIf([TimeAssigned] Like
"11:*PM",[CustExp2]+6,(IIf([TimeAssigned] Like
"12:*AM",[CustExp2]-8,(IIf([TimeAssigned] Like
"1:*AM",[CustExp2]-7,(IIf([TimeAssigned] Like
"2:*AM",[CustExp2]-6,[CustExp2])))))))))))))))))

When I continue to add the 3am, 4am, etc. I get an error message that
says
it is too complex. What does that mean? How can I fix it?
Click to expand...

Two suggestions: one would be to use Switch() rather than nested IIF; and
the
other - a bit more work but much simpler and more flexible in the long
run -
is to use a table.

Switch() takes arguments in pairs; evaluates the pairs left to right; and
returns the second member of the first pair encountered for which the
first
element is True. You can also make some considerable simplification by
not
repeating CustExp2 in every choice, and treating the time as a datetime
value
rather than as a string (if it IS a string you can use CDate() to convert
it
to a date/time):

After5: [CustExp2] + Switch(Hour([TimeAssigned])=18, 1,
Hour([TimeAssigned])=19, 2,
Hour([TimeAssigned[) = 20, 3,
<etc>

Or, to allow more flexibility (e.g. half-hour increments) create a three
field
table TimeFactors with StartTime, EndTime, and Factor:

#06:00PM# #07:00PM# 1
#07:00PM# #08:00PM# 2

etc.

Join this to your table using a "non equi join":

SELECT <other fields>, [Factor] * [CustExp2]
FROM yourtable INNER JOIN TimeFactors
ON yourtable.[TimeAssigned] >= Timefactors.StartTime
AND yourtable.[TimeAssigned] < Timefactors.EndTime

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
I get an error message "expression is too complex" when i do this:
Click to expand...

A Choose() function might be the best solution (if you don't want to use the
table-driven approach suggested):

Choose(Hour([TimeAssigned]) - 1, -8, -7, -6, ....

Choose takes an integer argument and returns the second argument if that
integer is 1, the third if it's 2, and so on - in your case you'll need 24
arguments. The Choose() function will be much smaller than the complex Switch,
and should avoid the size limit that's causing this error.

John W. Vinson [MVP]
 
Back
Top