大家好吧:) 我刚刚开始使用access / sql。 我马上遇到了这个问题SQL表达式过于复杂 ; 我知道谷歌了很多，而且我见过的唯一解决方法是什么， 只是改写我的代码/查询。 情况： 我正在将表格分类为它们的使用方式（打印，存储，只读等等）。 但是表格可以使用20种不同的方式， 所以我最初的方法是编写一个大的IFF语句， 整理每个案例。 然而，大约11个左右的IFF陈述，我开始得到这个错误， 所以我想知道是否有人对如何制作这个有任何想法排序更容易/可行。 代码： 这就是我的巨大IFF声明： 如果查询<> ""然后 query = query& ，iif（mq.ffileusage ="& Chr（34）&" 0"& Chr（34）&，& Chr（34）&仅查看& ; Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34）&"〜"& Chr（34）&" ，& Chr（34）&"仅查看"& Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34） &"""& 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）&&quo吨; 2英寸&安培; Chr（34）& "，" &安培; Chr（34）& 填写并打印 &安培; Chr（34）_ & "，iif（mq.ffileusage ="& Chr（34）&" ~2"& Chr（34）&"，& Chr（34）&"" Fill and Print" ;& Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34）&""& Chr（34）&" ;，"& Chr（34）&" Fill and Print&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）&""& 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 ="&am磷; Chr（34）& " H" &安培; Chr（34）& "，" &安培; Chr（34）& 纸质复印件 &安培; Chr（34）_ & ，iif（mq.ffileusage ="& Chr（34）&" ~H& Chr（34）&，& Chr（34）&Paper Copy" & Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34）&"& 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）&a熔点; "，" &安培; 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）&））））））））））））））））））））作为[Attatchment Usage] **基本上，我正在做的就是检查表格中的各种值（表单用途）， ，当它在excel中输出表单时，它会相应地标记它。 **即。如果读取的值是0，则读取的值是0。然后它将输出仅查看在excel文件中 我知道代码有效，因为如果我将IFF语句的数量限制为12，它将正常工作。 但是尽快随着我的添加，它会给我过于复杂的感觉。错误。 任何人都知道如何让这更简单？ 我想的可能是一个CASE函数或其他东西， 但我不确定，因为我几天前才开始使用sql。 感谢您的帮助：） k，40
解决方案大家好:) 我刚刚开始使用access / sql。 我马上遇到了这个问题SQL表达式太复杂了 我google 这意味着什么，以及我见过的唯一解决办法， 只是改写我的代码/查询。 情况： 我正在将表单分类为它们的使用方式（打印，存储，只读等等）。 但是表单可以有20种不同的方式使用， 所以我最初的方法是写一个大的IFF声明， 整理每个案例。 然而，大约11左右的IFF声明，我开始得到这个错误， 所以我想知道是否有人对如何使这种排序更容易有任何想法/ 代码： 这就是我的IFF声明：/ b如果查询<> ""然后 query = query& ，iif（mq.ffileusage ="& Chr（34）&" 0"& Chr（34）&，& Chr（34）&仅查看& ; Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34）&"〜"& Chr（34）&" ，& Chr（34）&"仅查看"& Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34） &"""& 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）&&quo吨; 2英寸&安培; Chr（34）& "，" &安培; Chr（34）& 填写并打印 &安培; Chr（34）_ & "，iif（mq.ffileusage ="& Chr（34）&" ~2"& Chr（34）&"，& Chr（34）&"" Fill and Print" ;& Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34）&""& Chr（34）&" ;，"& Chr（34）&" Fill and Print&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）&""& 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 ="&am磷; Chr（34）& " H" &安培; Chr（34）& "，" &安培; Chr（34）& 纸质复印件 &安培; Chr（34）_ & ，iif（mq.ffileusage ="& Chr（34）&" ~H& Chr（34）&，& Chr（34）&Paper Copy" & Chr（34）_ &"，iif（mq.ffileusage ="& Chr（34）&"& 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）&a熔点; "，" &安培; 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）&））））））））））））））））））））作为[Attatchment Usage] **基本上，我正在做的就是检查表格中的各种值（表单用途）， ，当它在excel中输出表单时，它会相应地标记它。 **即。如果读取的值是0，则读取的值是0。然后它将输出仅查看在excel文件中 我知道代码有效，因为如果我将IFF语句的数量限制为12，它将正常工作。 但是尽快随着我的添加，它会给我过于复杂的感觉。错误。 任何人都知道如何让这更简单？ 我想的可能是一个CASE函数或其他东西， 但我不确定，因为我几天前才开始使用sql。 感谢您的帮助：） k，40
我不知道Access，但我知道在Excel中，嵌套的if语句只能达到7层。我尝试进行快速谷歌搜索，但没有找到任何关于Access的最大层数，但这可能是你的问题？
你好mlcampeau， 在Access中我可以设法编写13个IFF语句（如上所示）， 但是当我超过13时，我开始得到太复杂的信息。错误。 所以我想知道如何更有效地编写这20个IFF语句， 所以它不是太复杂 感谢您的帮助，:) k，40
我遇到同样的问题访问，表达是wayyyyyyyyyyyyyyyy太复杂！ 选择案例几乎总是最好的方式，如果你正在谈论超过3或4个选项，并且当然是这类情况的唯一选择，有十多种可能性！它将使您的代码现在更容易理解，并且将来更容易维护。 祝你好运！ Linq 0）>
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
解决方案 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,40I 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,40I 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)>更多推荐
SQL表达式太复杂了
发布评论