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

[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>>>>MyCrite ria
in the criteria row of a query grid>>>>MyCrite ria()

[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.Va lue = 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(ffile usage 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(ffile usage 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.Va lue = 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(ffile usage 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
tblSignatureCon version
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.Va lue = 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(ffile usage 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(ffile usage 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.ffileattatch ement 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_ATTACHMEN T

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.ffileattatc hment' 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(ffile usage 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