Hi everyone :)
I just recently started using access/sql.
and right away I ran into this problem "SQL expression too complex"
I google'd a lot on what it means, and the only workaround I've seen sofar,
is to just rewrite my code/query.
Situation:
I am sorting forms into the way they are used (print,storage, readonly,etc... ).
However there are 20 different ways a form can be used,
so my initial approach was to write one big IFF statement,
sorting out each case.
however, around 11 or so IFF statements, I started to get this error,
so i was wondering if anyone had any ideas on how to make this sorting easier/workable.
Code:
This is what my huge IFF statement looks like:
If query <> "" Then
query = query & ",iif(mq.ffileu sage=" & Chr(34) & "0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~1~2" & Chr(34) & "," & Chr(34) & "View and Print and Fill and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "2~3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "4" & Chr(34) & "," & Chr(34) & "Print and Fill and Mail" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "Fill Print Save" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "S" & Chr(34) & "," & Chr(34) & "Source Application" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H~0" & Chr(34) & "," & Chr(34) & "xxxxxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H~1" & Chr(34) & "," & Chr(34) & "xxxxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H~2" & Chr(34) & "," & Chr(34) & "xxxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H~1" & Chr(34) & "," & Chr(34) & "xxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H~2~1" & Chr(34) & "," & Chr(34) & "xxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H~1~4" & Chr(34) & "," & Chr(34) & "xx" & Chr(34) _
& "," & Chr(34) & "" & Chr(34) & ")))))))))))))) )))))) as [Attatchment Usage]"
**basically, all im doing is checking the table for various values (form uses),
and when it outputs the form use in excel it will label it accordingly.
** ie. if the value read is "0" then it will output "View Only" in the excel document
I know the code works, because if I limit the amount of IFF statements to 12, it will work fine.
But as soon as I add anymore, it will give me the "too complex" error.
Anyone have an idea of how I could make this simplier?
I was thinking maybe a CASE function or something,
but I'm not sure, because I've only started sql a few days ago.
Thanks for your help :)
k,40
19  3643 
Hi everyone :)
I just recently started using access/sql.
and right away I ran into this problem "SQL expression too complex"
I google'd a lot on what it means, and the only workaround I've seen sofar,
is to just rewrite my code/query.
Situation:
I am sorting forms into the way they are used (print,storage, readonly,etc... ).
However there are 20 different ways a form can be used,
so my initial approach was to write one big IFF statement,
sorting out each case.
however, around 11 or so IFF statements, I started to get this error,
so i was wondering if anyone had any ideas on how to make this sorting easier/workable.
Code:
This is what my huge IFF statement looks like:
If query <> "" Then
query = query & ",iif(mq.ffileu sage=" & Chr(34) & "0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~1~2" & Chr(34) & "," & Chr(34) & "View and Print and Fill and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "2~3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "4" & Chr(34) & "," & Chr(34) & "Print and Fill and Mail" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "Fill Print Save" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "S" & Chr(34) & "," & Chr(34) & "Source Application" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H~0" & Chr(34) & "," & Chr(34) & "xxxxxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H~1" & Chr(34) & "," & Chr(34) & "xxxxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "~H~2" & Chr(34) & "," & Chr(34) & "xxxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H~1" & Chr(34) & "," & Chr(34) & "xxxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H~2~1" & Chr(34) & "," & Chr(34) & "xxx" & Chr(34) _
& ",iif(mq.ffileu sage=" & Chr(34) & "H~1~4" & Chr(34) & "," & Chr(34) & "xx" & Chr(34) _
& "," & Chr(34) & "" & Chr(34) & ")))))))))))))) )))))) as [Attatchment Usage]"
**basically, all im doing is checking the table for various values (form uses),
and when it outputs the form use in excel it will label it accordingly.
** ie. if the value read is "0" then it will output "View Only" in the excel document
I know the code works, because if I limit the amount of IFF statements to 12, it will work fine.
But as soon as I add anymore, it will give me the "too complex" error.
Anyone have an idea of how I could make this simplier?
I was thinking maybe a CASE function or something,
but I'm not sure, because I've only started sql a few days ago.
Thanks for your help :)
k,40
I don't know about Access, but I know that in Excel, a nested if statement can only be up to 7 layers. I tried doing a quick google search and didn't find anything about a maximum number of layers for Access, but that may be your problem?
Hi mlcampeau,
In Access I can manage to write 13 IFF statements (like shown above),
but as soon as I go over 13 I start getting the "too complex" error.
So I'm wondering how I could write these 20 IFF statements more efficiently,
so it is not "too complex"
thanks for helping, :)
k,40
I have the same problem Access has, the expression is wayyyyyyyyyyyyy yy too complex! Select Case is almost always the best way to go if you're talking about more than 3 or 4 options, and certainly is the only choice for this type of situation with over a dozen possibilities! It'll make your code easier to understand now, and much easier to maintain in the future.
Good Luck!
Linq ;0)>
Hey missinglinq,
I'm trying to write a Case statement as we speak,
but I am very unfamiliar with it, and I'm using just a sample I found online.
But I think the way I set up my code,
it is not working as simple as:
If q_attfileusage < 0 And variableInfo.Va lue = 1 Then
If query <> "" Then
query = query & ", CASE fileusage WHEN '0' THEN 'View Only' WHEN '~0' THEN 'View Only' END as [Atth Usage]"
How would I turn the IFF statements into a CASE?
Thanks,
k40
I have the same problem Access has, the expression is wayyyyyyyyyyyyy yy too complex! Select Case is almost always the best way to go if you're talking about more than 3 or 4 options, and certainly is the only choice for this type of situation with over a dozen possibilities! It'll make your code easier to understand now, and much easier to maintain in the future.
Good Luck!
Linq ;0)>
Hey missinglinq,
I'm trying to write a Case statement as we speak,
but I am very unfamiliar with it, and I'm using just a sample I found online.
But I think the way I set up my code,
it is not working as simple as:
If q_attfileusage < 0 And variableInfo.Va lue = 1 Then
If query <> "" Then
query = query & ", CASE fileusage WHEN '0' THEN 'View Only' WHEN '~0' THEN 'View Only' END as [Atth Usage]"
How would I turn the IFF statements into a CASE?
Thanks,
k40
Yeah, unfortunately I'm useless on this one because I've never used CASE before. Sorry! Hopefully linq or someone else can guide you in the right direction.
Yeah, unfortunately I'm useless on this one because I've never used CASE before. Sorry! Hopefully linq or someone else can guide you in the right direction.
Hope this helps.
You can’t directly use a select case statement in an Access Query, but you can create a user defined function that uses the select case statement as illustrated below. Here is what you do:
1. copy and paste the function to a standard module (it needs to be a public function to be used with a query).
2. You can reference the function in the criteria row of the ffileusage column in your query grid as follows: MyCriteria()
or
reference the function in your code like you were doing with the IIf statements.
_______________ _______________ _______________ ___
Public Function MyCriteria (ffileusage As Integer) As String
Select Case ffileusage
Case = 0 MyCriteria = “View Only” Case = 1 MyCriteria = "View and Print" Case = 2 MyCriteria = "View and Print and Fill and Print" Case Else MyCriteria = “” End Select
End Function
FishVal 2,653
Recognized Expert Specialist
Hey missinglinq,
I'm trying to write a Case statement as we speak,
but I am very unfamiliar with it, and I'm using just a sample I found online.
But I think the way I set up my code,
it is not working as simple as:
If q_attfileusage < 0 And variableInfo.Va lue = 1 Then
If query <> "" Then
query = query & ", CASE fileusage WHEN '0' THEN 'View Only' WHEN '~0' THEN 'View Only' END as [Atth Usage]"
How would I turn the IFF statements into a CASE?
Thanks,
k40
Hi!
Unfortunately Access does not support SQL2 standard which supports CASE-WHEN-THEN-ELSE statement.
You can use Switch function instead of nested IIf's. I think this will fit your needs. Plz see Access help topic concerning "Switch" function.
Good luck.
great, thanks very much for the help.
I'm going to try correcting this problem now... let you guys know how it goes.
k,40
ok, sorry I am confused
When I write either the CASE/Switch function, how do I get the output in excel to be all in a column called [File Usage].
I cannot figure out where to put the "AS" part of the code.
Also, if I make a public function for the CASE statement,
how do I reference it??
Thanks,
k40
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Christos TZOTZIOY Georgiou |
last post by:
Hi all,
this post contains at the end a handy module that I've used quite often
when I wanted to analyse the occasional complex expression and how it
was to be evaluated.
The function analyse_expression is called with a single string argument
containing an expression. Names are allowed (actually, preferred over
numbers ;-), since the function eval's in a protected dictionary, where
names are generated as needed.
|
by: |
last post by:
I am trying to run the following agregate function in a parameterized query
on Access2000:
Min(.*sqr(./.)/)
The query saved OK, but an attempt to run it results in the message:
The expression is typed incorrectly or it is too complex to be evaluated
If the sintax correct? Perhaps it is, otherwise it would not save.
What can be done about it?
|
by: Tom |
last post by:
I have struggled with the issue of whether or not to use Regular
Expressions for a long time now, and after implementing many text
manipulating solutions both ways, I've found that writing specialized
code instead of an RE is almost always the better solution. Here is
why....
RE's are complex. Sure it is one line of code, but it is on hell of a
line. Some of my RE remind me of the obfuscated code contest winners,
where one line of...
|
by: Bob Stearns |
last post by:
Is there an easy way (without duplication of the complex expression) to
use the same complex expression in all three places?
Will something like this work?
WITH (SELECT t.*, <complex expr> AS x FROM <base_table> AS t) AS wt
SELECT * FROM wt WHERE ABS(x)<10 ORDER BY ABS(x)
|
by: Mikel |
last post by:
I am trying to get around the problem "The expression you have entered
is too complex" for a select query. (The example below is not the
expression that is giving me headaches.)
So I am thinking that I just need to do the parsing and calculating in
an event procedure for an "On Click" event.
My question is:
If I have a query field in access97 that parses date (in format
| | |
by: Andrew |
last post by:
How can I create a property or function for a typed datasets column?
By this I need to add a new column (element) to the table or override an
existing column.
For instance I have an Invoice table that needs to have a Status property.
This status property has to read the other elements in the table to
determine what the status is and return a string.
I know that I can enter this in the expression fields, but as the
expressions become more...
|
by: Marc Gravell |
last post by:
In Linq, you can apparently get a meaningful body from and
expression's .ToString(); random question - does anybody know if linq
also includes a parser? It just seemed it might be a handy way to
write a safe but easy implementation (i.e. no codedom) for an
IBindingListView.Filter (by compiling to a Predicate<T>).
Anybody know if this is possible at all?
Marc
|
by: jessicaeatworld |
last post by:
Hi,
I'm using Access 2003 on Windows XP.
My Error:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
I'm guessing that one of these calcs is the problem:
(/)*31 AS , (/)*31 AS , (/)*31 AS , / AS
|
by: dspfun |
last post by:
Hi!
The words "expression" and "statement" are often used in C99 and C-
textbooks, however, I am not sure of the clear defintion of these
words with respect to C.
Can somebody provide a sharp defintion of "expression" and
"statement"? What is the difference between an expression and a
statement?
|
by: Coll |
last post by:
I'm receiving this message when running a query...
This expression is typed incorrectly or is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.
Here's the background. I have a query. I've figured out through
cutting and pasting which field is the culprit. So I have a field with
the following info...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
