epxression

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
 
D

Douglas J. Steele

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

John W. Vinson

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

Guest

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

John W. Vinson

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

Guest

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

BruceM

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

John W. Vinson

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Standard Deviation 3
Stand Deviation Expression 6
Super complex if statement 12
Calculating Date and Time using IIf statements - Please Help Me 1
Period End Dates Table ... How to Link? 3
RESERVED WORD or AGRUMENT NAME 3
Formatting Dates 10
elapsed time calculation help needed 1

Top