SQL expression too complex

[kawaks40]

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.ffileusage=" & Chr(34) & "0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1~2" & Chr(34) & "," & Chr(34) & "View and Print and Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2~3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "4" & Chr(34) & "," & Chr(34) & "Print and Fill and Mail" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "Fill Print Save" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "S" & Chr(34) & "," & Chr(34) & "Source Application" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~0" & Chr(34) & "," & Chr(34) & "xxxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~1" & Chr(34) & "," & Chr(34) & "xxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~2" & Chr(34) & "," & Chr(34) & "xxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~1" & Chr(34) & "," & Chr(34) & "xxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~2~1" & Chr(34) & "," & Chr(34) & "xxx" & Chr(34) _
& ",iif(mq.ffileusage=" & 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

[mlcampeau]

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.ffileusage=" & Chr(34) & "0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~0" & Chr(34) & "," & Chr(34) & "View Only" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1" & Chr(34) & "," & Chr(34) & "View and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~1~2" & Chr(34) & "," & Chr(34) & "View and Print and Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~2" & Chr(34) & "," & Chr(34) & "Fill and Print" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "2~3" & Chr(34) & "," & Chr(34) & "Fill and Print and Submit" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "4" & Chr(34) & "," & Chr(34) & "Print and Fill and Mail" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "5" & Chr(34) & "," & Chr(34) & "Fill Print Save" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H" & Chr(34) & "," & Chr(34) & "Paper Copy" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "S" & Chr(34) & "," & Chr(34) & "Source Application" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~0" & Chr(34) & "," & Chr(34) & "xxxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~1" & Chr(34) & "," & Chr(34) & "xxxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "~H~2" & Chr(34) & "," & Chr(34) & "xxxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~1" & Chr(34) & "," & Chr(34) & "xxxx" & Chr(34) _
& ",iif(mq.ffileusage=" & Chr(34) & "H~2~1" & Chr(34) & "," & Chr(34) & "xxx" & Chr(34) _
& ",iif(mq.ffileusage=" & 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?

[kawaks40]

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

[missinglinq]

I have the same problem Access has, the expression is wayyyyyyyyyyyyyyy 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)>

[kawaks40]

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.Value = 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 wayyyyyyyyyyyyyyy 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)>

[mlcampeau]

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.Value = 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.

[puppydogbuddy]

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]

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.Value = 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.

[kawaks40]

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

[kawaks40]

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

[FishVal]

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

Replace nested IIf's with Switch function in your query SQL expression. For Switch function syntax see Access help.

[puppydogbuddy]

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

<<<<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].>>>>
In the sample you provided, you used ffileusage...if it should be [File Usage], then replace ffileusage with [File Usage] in the code I provided.

<<<<<Also, if I make a public function for the CASE statement,
how do I reference it??>>>

in VBA code>>>>MyCriteria
in the criteria row of a query grid>>>>MyCriteria()

[kawaks40]

:) ok, that helped a lot
Now it sorts properly... except one minor detail.

I am now getting the error
"Repository Report Generator was unable to append all the data to the table"

- it ways that 6280 record(s) were deleted, and 0 record(s) were lost
*If data was deleted, the data you pasted or imported doesn't match
the field data type or the FieldSize property in the destination table.


My code looks like this:

The main query in the form is:

If q_attfileusage < 0 And variableInfo.Value = 1 Then
If query <> "" Then
query = query & ",FileUsage(mq.ffileusage) as [Atth Usage]"



Then I made a module, and made a public function ilke this:

Public Function FileUsage(ffileusage As String)
Select Case ffileusage
Case Is = "0"
FileUsage = "View Only"
Case Is = "~0"
FileUsage = "View Only"
Case Is = "1"
FileUsage = "View and Print"
Case Is = "~1"
FileUsage = "View and Print"
Case Is = "~1~2"
FileUsage = "View and Print, Fill and Print"
Case Is = "2"
FileUsage = "Fill and Print"
Case Is = "~2"
FileUsage = "Fill and Print"
Case Is = "3"
FileUsage = "Fill, Print and Submit"
Case Is = "2~3"
FileUsage = "Fill, Print and Submit"
Case Is = "4"
FileUsage = "Print Fill and Mail"
Case Is = "5"
FileUsage = "Fill, Print and Save"
Case Is = "H"
FileUsage = "Paper Copy"
Case Is = "~H"
FileUsage = "Paper Copy"
Case Is = "~H~0"
FileUsage = "Paper Copy, View Only"
Case Is = "~H~1"
FileUsage = "Paper Copy, View and Print"
Case Is = "~H~2"
FileUsage = "Paper Copy, Fill and Print"
Case Is = "H~1"
FileUsage = "Paper Copy, View and Print"
Case Is = "H~2~1"
FileUsage = "Paper Copy, View, Fill and Print"
Case Is = "H~1~4"
FileUsage = "Print, Fill and Mail"
Case Is = "S"
FileUsage = "Source Application"
Case Else
FileUsage = " - "
End Select
End Function




When I run it, it seems to work,
except there are a number of forms it lists with no "file usage" status at all
(just blank)
But there are also a number of forms that it lists correctly.


So I am unsre if it something to do with my coding.
Or is it becase the database that I am working with is incomplete

(from what I googled, I found that this may be due to the fact that
there are a number of records with unacceptable input or empty)


thanks again for all the help,
and patience ;p

k,40

[kawaks40]

the only thing I can think of is in:

Public Function FileUsage(ffileusage As String)

ffileusage >> is that right?
That is supposed to refer to an external piece of information.
I think it should be mq.ffileusage (like the IFF statements refered to)
*But I get an error everytime I try to replace it.


or, it could be in

query = query & ",FileUsage(mq.ffileusage) as [Atth Usage]"
I am trying to pass mq.ffileusage into my function.




.... so I think I am telling the query to search the wrong location.

[FishVal]

:) ok, that helped a lot
Now it sorts properly... except one minor detail.

I am now getting the error
"Repository Report Generator was unable to append all the data to the table"

- it ways that 6280 record(s) were deleted, and 0 record(s) were lost
*If data was deleted, the data you pasted or imported doesn't match
the field data type or the FieldSize property in the destination table.


My code looks like this:

The main query in the form is:

If q_attfileusage < 0 And variableInfo.Value = 1 Then
If query <> "" Then
query = query & ",FileUsage(mq.ffileusage) as [Atth Usage]"



Then I made a module, and made a public function ilke this:

Public Function FileUsage(ffileusage As String)
Select Case ffileusage
Case Is = "0"
FileUsage = "View Only"
Case Is = "~0"
FileUsage = "View Only"
Case Is = "1"
FileUsage = "View and Print"
Case Is = "~1"
FileUsage = "View and Print"
Case Is = "~1~2"
FileUsage = "View and Print, Fill and Print"
Case Is = "2"
FileUsage = "Fill and Print"
Case Is = "~2"
FileUsage = "Fill and Print"
Case Is = "3"
FileUsage = "Fill, Print and Submit"
Case Is = "2~3"
FileUsage = "Fill, Print and Submit"
Case Is = "4"
FileUsage = "Print Fill and Mail"
Case Is = "5"
FileUsage = "Fill, Print and Save"
Case Is = "H"
FileUsage = "Paper Copy"
Case Is = "~H"
FileUsage = "Paper Copy"
Case Is = "~H~0"
FileUsage = "Paper Copy, View Only"
Case Is = "~H~1"
FileUsage = "Paper Copy, View and Print"
Case Is = "~H~2"
FileUsage = "Paper Copy, Fill and Print"
Case Is = "H~1"
FileUsage = "Paper Copy, View and Print"
Case Is = "H~2~1"
FileUsage = "Paper Copy, View, Fill and Print"
Case Is = "H~1~4"
FileUsage = "Print, Fill and Mail"
Case Is = "S"
FileUsage = "Source Application"
Case Else
FileUsage = " - "
End Select
End Function




When I run it, it seems to work,
except there are a number of forms it lists with no "file usage" status at all
(just blank)
But there are also a number of forms that it lists correctly.


So I am unsre if it something to do with my coding.
Or is it becase the database that I am working with is incomplete

(from what I googled, I found that this may be due to the fact that
there are a number of records with unacceptable input or empty)


thanks again for all the help,
and patience ;p

k,40

Hi, there.

I think that the most natural, flexible and suitable solution would be the following.

Create additional table
tblSignatureConversion
txtSignature Text
txtConversion Text

and fill it with you values, e.g.
txtSignature txtConversion
0 ViewOnly
~0 ViewOnly
1 View and Print
~1 View and Print
.....
and so on

then use tables join to replace value in original table with correspondent text

SELECT *, tblSignatureConversion.txtConversion FROM mq JOIN tblSignatureConversion ON mq.ffileusage=tblSignatureConversion.txtSignature;
 

P.S. BTW join query runs faster than that with VBA function in WHERE clause.

[puppydogbuddy]

:) ok, that helped a lot
Now it sorts properly... except one minor detail.

I am now getting the error
"Repository Report Generator was unable to append all the data to the table"

- it ways that 6280 record(s) were deleted, and 0 record(s) were lost
*If data was deleted, the data you pasted or imported doesn't match
the field data type or the FieldSize property in the destination table.


My code looks like this:

The main query in the form is:

If q_attfileusage < 0 And variableInfo.Value = 1 Then
If query <> "" Then
query = query & ",FileUsage(mq.ffileusage) as [Atth Usage]"



Then I made a module, and made a public function ilke this:

Public Function FileUsage(ffileusage As String)
Select Case ffileusage
Case Is = "0"
FileUsage = "View Only"
Case Is = "~0"
FileUsage = "View Only"
Case Is = "1"
FileUsage = "View and Print"
Case Is = "~1"
FileUsage = "View and Print"
Case Is = "~1~2"
FileUsage = "View and Print, Fill and Print"
Case Is = "2"
FileUsage = "Fill and Print"
Case Is = "~2"
FileUsage = "Fill and Print"
Case Is = "3"
FileUsage = "Fill, Print and Submit"
Case Is = "2~3"
FileUsage = "Fill, Print and Submit"
Case Is = "4"
FileUsage = "Print Fill and Mail"
Case Is = "5"
FileUsage = "Fill, Print and Save"
Case Is = "H"
FileUsage = "Paper Copy"
Case Is = "~H"
FileUsage = "Paper Copy"
Case Is = "~H~0"
FileUsage = "Paper Copy, View Only"
Case Is = "~H~1"
FileUsage = "Paper Copy, View and Print"
Case Is = "~H~2"
FileUsage = "Paper Copy, Fill and Print"
Case Is = "H~1"
FileUsage = "Paper Copy, View and Print"
Case Is = "H~2~1"
FileUsage = "Paper Copy, View, Fill and Print"
Case Is = "H~1~4"
FileUsage = "Print, Fill and Mail"
Case Is = "S"
FileUsage = "Source Application"
Case Else
FileUsage = " - "
End Select
End Function




When I run it, it seems to work,
except there are a number of forms it lists with no "file usage" status at all
(just blank)
But there are also a number of forms that it lists correctly.


So I am unsre if it something to do with my coding.
Or is it becase the database that I am working with is incomplete

(from what I googled, I found that this may be due to the fact that
there are a number of records with unacceptable input or empty)


thanks again for all the help,
and patience ;p

k,40

1. change: If query <> "" Then
to: If query <> "" And query Is Not Null Then

2. mq.ffileusage vs ffileusage????
query = query & ",FileUsage(mq.ffileusage) as [Atth Usage]"
vs, Public Function FileUsage(ffileusage As String)

[kawaks40]

hey guys, very sorry about this...

I think I am giving the query the wrong location.
I am using an existing database so I was using mq.ffileattatchement based on the fact that all other queries were using that same style.
*but once I looked over the records it was returning
I realized that some records were not found on the table I thought it was looking at.
*and also, there are no blank fields in the table at all.
They are all filled with the appropriate case variables (0,~0,1,~1,etc..)
(so there should not be any blank returns at all)



Without looking at my previous code/description,,,
if I had a database with a table called FORMS_ATTACHMENT

and in that table I wanted to look at the column named "FILEUSAGE"

what would be the right way?



I have no idea actually what 'mq.ffileattatchment' is pointing to,
I tried to trace it back so I could see what records are coming up as blank.
Other queries seem to work fine with it, so I used the same format of 'mq'.



thanks again :)

[kawaks40]

ok, fixed... ignore that last post :P
(the external value should not be declared as a string - in the public function)


thanks so much for the help, I really appreciate it.
I learned a lot from this.


the only thing I am working on now,
is that when I try to narrow my search to just "View Only" for example.

It will return all results that were
- null
- View Only
- Paper Copy + View Only



but thank you very very much for the help!
I am happy just being able to fix the 'too complex' problem

thanks,
k40

[puppydogbuddy]

ok, fixed... ignore that last post :P
(the external value should not be declared as a string - in the public function)


thanks so much for the help, I really appreciate it.
I learned a lot from this.


the only thing I am working on now,
is that when I try to narrow my search to just "View Only" for example.

It will return all results that were
- null
- View Only
- Paper Copy + View Only



but thank you very very much for the help!
I am happy just being able to fix the 'too complex' problem

thanks,
k40

1. what did you declare external value as??

2. What is ~0 vs 0 ??
Public Function FileUsage(ffileusage As String)
Select Case ffileusage
Case Is = "0" <<<<<<<<<<<<<<<<<<<<<<<
FileUsage = "View Only"
Case Is = "~0" <<<<<<<<<<<<<<<<<<<<<<<????
FileUsage = "View Only"

[kawaks40]

HI :)


1. I didn't declare the type of value being inputted.
I just left it as "ffileusage" (not - ffileusage as String)


2. 0 and ~0 aren't operators/functions...
they're just inputted to determine the usage of the files that I am sorting.
so:
0 indicates 'read only'
~0 indicates 'read only and print'








:) thanks for all the help,again