访问 SQL IIF 复合体
我正在尝试使用 iif 语句将一列数据状态更改为另一列数据状态 （即：iif([major]="eet","电子工程技术")）。
我让它工作，直到我向名为 PLS 的组添加了一个新专业，我得到一个框，指出表达式太复杂。
当我把那个拿出来时，效果很好。访问的 iif 语句数量是否有限制？
Majors: IIf([Major]="EET","Electronics Engineering Technology",
IIf([Major]="DMA","Digital Media Arts Technology",
IIf([Major]="BAM","Business Administration - Management ",
IIf([Major]="Ess","Industrial Electronics Technology - Electronic Security Systems",
IIf([Major]="FMT","Facilities Management Technology",
IIf([Major]="FMTC","Facilities Management Technology Certificate",
IIf([Major]="HIT","Health Information Technology",
IIf([Major]="HSE","Human Services",
IIf([Major]="HVAC","Heating, Ventilation, Air Conditioning and Refrigeration Technology",
IIf([Major]="IENET","Industrial Electronics Technology - Computer and Networking Track",
IIf([Major]="Auto","Automotive Technology",
IIf([Major]="AT","Accounting Technology",
IIf([Major]="IETC","Industrial Electronics Technology- Computer Track",
IIf([Major]="IETR","Industrial Electronics Technology - Railway Electronics Systems",
IIf([Major]="PLS","test","Ophthalmic Dispensing"))))))))))))))
I am trying to change what one column data states to another using iif statements
(ie: iif([major]="eet","electronic engineering technology")).
I got it to work until I added a new major to the group called PLS I get an box that states the expression is too complex.
When I take that one out it works fine. Is there a limit to the amount of iif statement for access?
Majors: IIf([Major]="EET","Electronics Engineering Technology",
IIf([Major]="DMA","Digital Media Arts Technology",
IIf([Major]="BAM","Business Administration - Management ",
IIf([Major]="Ess","Industrial Electronics Technology - Electronic Security Systems",
IIf([Major]="FMT","Facilities Management Technology",
IIf([Major]="FMTC","Facilities Management Technology Certificate",
IIf([Major]="HIT","Health Information Technology",
IIf([Major]="HSE","Human Services",
IIf([Major]="HVAC","Heating, Ventilation, Air Conditioning and Refrigeration Technology",
IIf([Major]="IENET","Industrial Electronics Technology - Computer and Networking Track",
IIf([Major]="Auto","Automotive Technology",
IIf([Major]="AT","Accounting Technology",
IIf([Major]="IETC","Industrial Electronics Technology- Computer Track",
IIf([Major]="IETR","Industrial Electronics Technology - Railway Electronics Systems",
IIf([Major]="PLS","test","Ophthalmic Dispensing"))))))))))))))
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
一种选择是使用 Switch() 语句而不是 IIf()：虽然 VBA Switch 函数似乎接受大量参数（我去了 22 个级别）在我停止测试之前），Jet/ACE 数据库引擎的 Switch 实现似乎上限为 14 个级别。正如您正确指出的那样，它会因 15 个或更多而窒息。
这似乎使查找表（如下所述）成为唯一实用的选择。 （还有其他不太实用的选择，例如编写自己的 Switch() 函数，它接受任意大量的参数，但我认为当查找表在这里是一个如此明显的选择时，这是愚蠢的。）
更好的选择是创建一个查找表并将其连接到您的查询。如果您可以确定查找表中每个“主要”缩写都有一行，请使用 INNER JOIN。如果您不确定，请使用带有 Nz() 或 IIf() 的 OUTER JOIN。
One option would be to use a Switch() statement instead of the IIf():While the VBA Switch Function appears to accept a large number of parameters (I went 22 levels deep before I stopped testing), the Jet/ACE db engine's implementation of Switch appears to be capped at a maximum of 14 levels. As you correctly pointed out, it chokes on 15 or more.
That appears to leave the lookup table (described below) as the only practical option. (There are other less practical options, like writing your own Switch() function that takes an arbitrarily large number of parameters, but I think that is silly when the lookup table is such an obvious choice here.)
A better option would be to create a lookup table and JOIN it to your query. Use an INNER JOIN if you can be sure that there will be a row in the lookup table for every "Major" abbreviation. Use an OUTER JOIN with an Nz() or IIf() if you can't be sure.
如果您的后端位于 MS SQL Server 上并通过 ODBC 连接表，您可以在 SQL 代码中使用
CASE WHEN THEN END CASE语句，但在 MS Access 中则不能。您可能遇到的限制是 SQL 查询的堆栈深度限制：每个嵌套表达式都需要查询分析器更深入一步，并且在某个时间点您的堆栈耗尽，然后失败。此外，SQL 查询中的符号总数是有限制的，但这已经足够大了，应该没问题。
您的解决方案的一个可能的快速克服方法（尽管它实际上使您的查询变慢）是将值放入单独的表中并使用 MS 中的
DLookUp(Table, Field, Criteria)函数执行查找使用权。或者，您可以仅通过缩写连接表，然后将查询转换为完整的表（也称为物化视图）。If you have a back-end located on MS SQL Server and connect your tables via ODBC you could use the
CASE WHEN THEN END CASEstatement in SQL code, but in MS Access you can't.The limitation you are likely to bump into is the stack depth limitation for SQL queries: every nested expression requires query analyzer to go one step deeper and at certain time point your stack is depleted and then it fails. Besides, there is a limitation to the total number of symbols in a SQL query, but this is large enough and should be OK here.
A possible a quick overcome for your solution (though it makes your query effectively slower) is to put the values to the separate table and perform look-up using
DLookUp(Table, Field, Criteria)function in MS Access. Alternatively you can just join the table over your abbreviations and then convert your query into a full-fledged table (aka materialized view).我将使用这样的查询运行 VBA 循环
，其中 [LongName] 和 [ShortName] 是参数。
但实际上，您最好将缩写保留在列中并创建一个查找表，当您想要使用长名称时可以加入该查找表。就数据存储而言，它的效率要高得多。
I would run a VBA loop with a query like this
where [LongName] and [ShortName] are parameters.
But really you're better off leaving the abbreviations in the column and creating a lookup table that you can join to when you want to use the long names. It's far more efficient in terms of data storage.
创建一个两列查找表，填充您的
IIF映射，然后加入到该表。这将产生一些优点，例如引用完整性，当这些值更容易更改表而不是更改所有查询等时。Create a two column lookup table, populate with your
IIFmappings then join to this table. This will yield advantages e.g. referential integrity, when these values it is easier to change the table rather than change all the queries, etc.我要做的是为每个专业创建单独的查询，然后传递 iif 或 switch。最后，它可以帮助最终用户将数据导出到 powerpoint，因为他们不必对数据进行排序。
What I am going to do is create separate queries for each major and then pass either iif or switch. In the end it helps the end user with exporting the data to powerpoint because they won't have to sort through the data.
在达到 MS Access 中开关功能的限制后，我最终来到了这里。如果它对任何处于我位置的人有帮助，这就是我所做的。
我在 10 处达到了 switch 的限制。对我有用的是使用
&来组合两个 switch 语句。I ended up here after hitting the limit for the switch function in MS Access. In case it helps anyone in my position, here is what I did.
I hit the limit for switch at 10. What worked for me was to use
&to combine two switch statements.