bjennings
asked on
Access 2003 - Expression in query causes "Too Complex" error
Hello Everyone:
I have an append query with the following expression:
Expr: IIf([qrymateriallist]![job ] Like "*-0*";[qrymateriallist]![ Expr1]+"-1 ";IIf([qry materialli st]![job] Like "*-1*";[qrymateriallist]![ Expr1]+"-1 00";IIf([q rymaterial list]![job ] Like "*-2*";[qrymateriallist]![ Expr1]+"-2 00";IIf([q rymaterial list]![job ] Like "*-30*";[qrymateriallist]! [Expr1]+"- 300";IIf([ qrymateria llist]![jo b] Like "*-32*";[qrymateriallist]! [Expr1]+"- 300";IIf([ qrymateria llist]![jo b] Like "*-33*";[qrymateriallist]! [Expr1]+"- 330";IIf([ qrymateria llist]![jo b] Like "*-34*";[qrymateriallist]! [Expr1]+"- 330";IIf([ qrymateria llist]![jo b] Like "*-4*";[qrymateriallist]![ Expr1]+"-4 00";IIf([q rymaterial list]![job ] Like "*-5*";[qrymateriallist]![ Expr1]+"-5 00";IIf([q rymaterial list]![job ] Like "*-6*";[qrymateriallist]![ Expr1]+"-6 00";IIf([q rymaterial list]![job ] Like "*-7*";[qrymateriallist]![ Expr1]+"-7 00";IIf([q rymaterial list]![job ] Like "*-8*";[qrymateriallist]![ Expr1]+"-8 00";IIf([q rymaterial list]![job ] Like "*-9*";[qrymateriallist]![ Expr1]+"-9 00";[qryma teriallist ]![Expr1]) )))))))))) ))
I need to add more iif statements, when I do I get the "too Complex" error message.
Is there anyway to work around this? This append query is part of on click event.
Thanks
I have an append query with the following expression:
Expr: IIf([qrymateriallist]![job
I need to add more iif statements, when I do I get the "too Complex" error message.
Is there anyway to work around this? This append query is part of on click event.
Thanks
Yeah - you'll have (admittedly in a roundabout way) passed 30 parameters in a function in SQL.
(The limit is round about there - and must apply to nested parameters too :-S)
That many Immediate Ifs are a) a nightmare to maintain and b) likely to give you no performance advantage over a VBA function.
And since it's just a simple text comparison (once you've passed the field value to your function) then it should be plenty quick!
(The limit is round about there - and must apply to nested parameters too :-S)
That many Immediate Ifs are a) a nightmare to maintain and b) likely to give you no performance advantage over a VBA function.
And since it's just a simple text comparison (once you've passed the field value to your function) then it should be plenty quick!
ASKER
I am not sure what to do. Create an sql append query using a function as an If statement? I am a little confused.
Can you use a function in a query?
Thanks!
Can you use a function in a query?
Thanks!
Sorry - this one seemed to get overlooked somehow. (I think I was away on hols that week actually - amazingly).
If ever a question seems to have fallen by the wayside - then just make a post with "Anyone home?" or "Any thoughts?" - you know... some kind of prompt.
It's not deliberate - but it happens, we just need a little reminder :-)
As it goes (and you'll have discovered yourself by now) yes you can use a VBA function in a query (we wouldn't have suggested it otherwise ;-).
And even if I'd kept up to date with this - there's every chance I'd have answered with an enigmatic "try it".
I think the 30 parameter limit has been mentioned in other questions in the PAQ (well - I know it has, I've mentioned it) and the use of a function with a concatenated field parameter etc as a solution.
Did you follow the advice that we did give that far?
Use a function to do the work?
If ever a question seems to have fallen by the wayside - then just make a post with "Anyone home?" or "Any thoughts?" - you know... some kind of prompt.
It's not deliberate - but it happens, we just need a little reminder :-)
As it goes (and you'll have discovered yourself by now) yes you can use a VBA function in a query (we wouldn't have suggested it otherwise ;-).
And even if I'd kept up to date with this - there's every chance I'd have answered with an enigmatic "try it".
I think the 30 parameter limit has been mentioned in other questions in the PAQ (well - I know it has, I've mentioned it) and the use of a function with a concatenated field parameter etc as a solution.
Did you follow the advice that we did give that far?
Use a function to do the work?
ASKER
Sorry for the delay, I was on vacation for a few days, then got sick.
This append query is part of a onclick event with 8 other queries. What I ended up doing was to remove the expression and let the query run. It appends the data to a temp table. Then a pieced some code together to run through the tbltemp and update any records that fall with my critera.
It looks somethink like this now:
docmd.query1
docmd.query2
call mysub
docmd.query3
docmd.query4
....
Thanks for your help. I was not sure how to use a function within a query.
************
This is the code I used:
Open tbltemp table
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbltemp" )
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
'MsgBox (rs!Job)
If rs!Job Like "*-0*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-1"
rs.UPDATE
ElseIf rs!Job Like "*-1*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-100"
rs.UPDATE
ElseIf rs!Job Like "*-2*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-200"
rs.UPDATE
ElseIf rs!Job Like "*-30*" Or rs!Job Like "*-31*" Or rs!Job Like "*-32*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-300"
rs.UPDATE
ElseIf rs!Job Like "*-33*" Or rs!Job Like "*-34*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-330"
rs.UPDATE
ElseIf rs!Job Like "*-35*" Or rs!Job Like "*-36*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-350"
rs.UPDATE
ElseIf rs!Job Like "*-37*" Or rs!Job Like "*-38*" Or rs!Job Like "*-39*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-370"
rs.UPDATE
ElseIf rs!Job Like "*-4*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-400"
rs.UPDATE
ElseIf rs!Job Like "*-5*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-500"
rs.UPDATE
ElseIf rs!Job Like "*-6*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-600"
rs.UPDATE
ElseIf rs!Job Like "*-70*" Or rs!Job Like "71*" Or rs!Job Like "72*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-700"
rs.UPDATE
ElseIf rs!Job Like "*-73*" Or rs!Job Like "*-74*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-730"
rs.UPDATE
ElseIf rs!Job Like "*-75*" Or rs!Job Like "*-76*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-750"
rs.UPDATE
ElseIf rs!Job Like "*-77*" Or rs!Job Like "*-78*" Or rs!Job Like "*-79*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-770"
rs.UPDATE
ElseIf rs!Job Like "*-8*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-800"
rs.UPDATE
ElseIf rs!Job Like "*-9*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-900"
rs.UPDATE
Else
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5)
rs.UPDATE
End If
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
End Sub
This append query is part of a onclick event with 8 other queries. What I ended up doing was to remove the expression and let the query run. It appends the data to a temp table. Then a pieced some code together to run through the tbltemp and update any records that fall with my critera.
It looks somethink like this now:
docmd.query1
docmd.query2
call mysub
docmd.query3
docmd.query4
....
Thanks for your help. I was not sure how to use a function within a query.
************
This is the code I used:
Open tbltemp table
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbltemp"
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
'MsgBox (rs!Job)
If rs!Job Like "*-0*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-1"
rs.UPDATE
ElseIf rs!Job Like "*-1*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-100"
rs.UPDATE
ElseIf rs!Job Like "*-2*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-200"
rs.UPDATE
ElseIf rs!Job Like "*-30*" Or rs!Job Like "*-31*" Or rs!Job Like "*-32*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-300"
rs.UPDATE
ElseIf rs!Job Like "*-33*" Or rs!Job Like "*-34*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-330"
rs.UPDATE
ElseIf rs!Job Like "*-35*" Or rs!Job Like "*-36*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-350"
rs.UPDATE
ElseIf rs!Job Like "*-37*" Or rs!Job Like "*-38*" Or rs!Job Like "*-39*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-370"
rs.UPDATE
ElseIf rs!Job Like "*-4*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-400"
rs.UPDATE
ElseIf rs!Job Like "*-5*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-500"
rs.UPDATE
ElseIf rs!Job Like "*-6*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-600"
rs.UPDATE
ElseIf rs!Job Like "*-70*" Or rs!Job Like "71*" Or rs!Job Like "72*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-700"
rs.UPDATE
ElseIf rs!Job Like "*-73*" Or rs!Job Like "*-74*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-730"
rs.UPDATE
ElseIf rs!Job Like "*-75*" Or rs!Job Like "*-76*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-750"
rs.UPDATE
ElseIf rs!Job Like "*-77*" Or rs!Job Like "*-78*" Or rs!Job Like "*-79*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-770"
rs.UPDATE
ElseIf rs!Job Like "*-8*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-800"
rs.UPDATE
ElseIf rs!Job Like "*-9*" Then
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5) & "-900"
rs.UPDATE
Else
rs.Edit
rs!Parent_Component_Job = Left(rs!Job, 5)
rs.UPDATE
End If
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I know u can call them from SELECT queries, I assume u can do the same with APPEND
Failing that, just write the procedure in VBA
e.g.
SELECT a,b, MyFunc(c)
FROM table
Now pass in what u need to and get your public function MyFunc to return one value