Link to home
Start Free TrialLog in
Avatar of bjennings
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([qrymateriallist]![job] Like "*-1*";[qrymateriallist]![Expr1]+"-100";IIf([qrymateriallist]![job] Like "*-2*";[qrymateriallist]![Expr1]+"-200";IIf([qrymateriallist]![job] Like "*-30*";[qrymateriallist]![Expr1]+"-300";IIf([qrymateriallist]![job] Like "*-32*";[qrymateriallist]![Expr1]+"-300";IIf([qrymateriallist]![job] Like "*-33*";[qrymateriallist]![Expr1]+"-330";IIf([qrymateriallist]![job] Like "*-34*";[qrymateriallist]![Expr1]+"-330";IIf([qrymateriallist]![job] Like "*-4*";[qrymateriallist]![Expr1]+"-400";IIf([qrymateriallist]![job] Like "*-5*";[qrymateriallist]![Expr1]+"-500";IIf([qrymateriallist]![job] Like "*-6*";[qrymateriallist]![Expr1]+"-600";IIf([qrymateriallist]![job] Like "*-7*";[qrymateriallist]![Expr1]+"-700";IIf([qrymateriallist]![job] Like "*-8*";[qrymateriallist]![Expr1]+"-800";IIf([qrymateriallist]![job] Like "*-9*";[qrymateriallist]![Expr1]+"-900";[qrymateriallist]![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
Avatar of rockiroads
rockiroads
Flag of United States of America image

You might be better off calling a VBA function to do it

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
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!
Avatar of bjennings
bjennings

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!
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?
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
ASKER CERTIFIED SOLUTION
Avatar of kodiakbear
kodiakbear

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial