?
Solved

Access 2003 - Expression in query causes "Too Complex" error

Posted on 2006-05-24
8
Medium Priority
?
364 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:bjennings
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16750710
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16751761
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!
0
 

Author Comment

by:bjennings
ID: 16762877
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!
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16838982
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?
0
 

Author Comment

by:bjennings
ID: 16897749
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
0
 
LVL 1

Accepted Solution

by:
kodiakbear earned 0 total points
ID: 16927288
Closed, 400 points refunded.
kb
Experts Exchange Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question