表达式过于复杂 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim s1 As Integer = val(Left("2017年秋季",4))
Dim SQL As String = "IIF(b.学期 Like '%秋%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like '%春%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级))))"
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 7,'七年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(Left(" & SQL & ",1) = 9,'九年级')))))))))"
Dim SQL2 As String = "Select b.学期,b.学生编号,学生姓名,"& SQL1 &" As 年级 Fr om (({学生基本信息} a Inner JOIN {缴退费信息} b ON a.学生编号 = b.学生编号) Inner JOIN {违规信息} c ON b.学生编号 = c.学生编号) Inner JOIN (Select 学生编号,max(缴费日期) As 缴费日期 Fr om {缴退费信息} WHERE 缴费项目 = '学费' And 缴费状态 = '已缴' GROUP BY 学生编号) n on b.学生编号 = n.学生编号 And b.缴费日期 = n.缴费日期 WHERE 缴费项目 = '学费' And 缴费状态 = '已缴' And c.审批状态 = '拒绝报名'"

错误信息：表达式过于复杂在查询表达式 'IIF(Left(IIF(b.学期 Like '%秋%',IIF(('2017' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('2017'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like '%春%',IIF(('2017' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('2017' - val(Left(b.学期,4))) *100 + val(班级)))),1) = ' 中。

请老师帮我看一下问题在哪？删除任意类似红色字段就可以，请老师执教，谢谢！

[此贴子已经被作者于2018/5/10 21:14:10编辑过]

IIF(Left(" & SQL & ",1) = 9, '九年级', null)

iif有三个参数的，如 iif(条件, 值1, 值2)

1、贴出可以的代码；

2、贴出不可以的代码；

可以代码：

Dim s1 As Integer = val(Left("2017年秋季",4))
Dim SQL As String = "IIF(b.学期 Like '%秋%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like '%春%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"'- val(Left(b.学期,4))) *100 + val(班级))))"
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(9 = 9,'九年级'))))))))"
Dim SQL2 As String = "Select b.学期,b.学生编号,学生姓名,"& SQL1 &" As 年级 Fr om (({学生基本信息} a Inner JOIN {缴退费信息} b ON a.学生编号 = b.学生编号) Inner JOIN {违规信息} c ON b.学生编号 = c.学生编号) Inner JOIN (Select 学生编号,max(缴费日期) As 缴费日期 Fr om {缴退费信息} WHERE 缴费项目 = '学费' And 缴费状态 = '已缴' GROUP BY 学生编号) n on b.学生编号 = n.学生编号 And b.缴费日期 = n.缴费日期 WHERE 缴费项目 = '学费' And 缴费状态 = '已缴' And c.审批状态 = '拒绝报名'"

不可以代码：

Dim s1 As Integer = val(Left("2017年秋季",4))
Dim SQL As String = "IIF(b.学期 Like '%秋%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like '%春%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"'- val(Left(b.学期,4))) *100 + val(班级))))"
Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 7,'七年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(9 = 9,'九年级'))))))))"
Dim SQL2 As String = "Select b.学期,b.学生编号,学生姓名,"& SQL1 &" As 年级 Fr om (({学生基本信息} a Inner JOIN {缴退费信息} b ON a.学生编号 = b.学生编号) Inner JOIN {违规信息} c ON b.学生编号 = c.学生编号) Inner JOIN (Select 学生编号,max(缴费日期) As 缴费日期 Fr om {缴退费信息} WHERE 缴费项目 = '学费' And 缴费状态 = '已缴' GROUP BY 学生编号) n on b.学生编号 = n.学生编号 And b.缴费日期 = n.缴费日期 WHERE 缴费项目 = '学费' And 缴费状态 = '已缴' And c.审批状态 = '拒绝报名'"

区别：可以

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(Left(" & SQL & ",1) = 9,'九年级'))))))))"

不可以

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 7,'七年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(Left(" & SQL & ",1) = 9,'九年级'))))))))"

[此贴子已经被作者于2018/5/10 21:47:13编辑过]

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 7,'七年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(9 = 9,'九年级',null)))))))))"

这样可以

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 7,'七年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(9 = 9,'九年级',null)))))))))"

这样不可以

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 7,'七年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(Left(" & SQL & ",1) = 9,'九年级',null)))))))))"

[此贴子已经被作者于2018/5/10 21:53:25编辑过]

应该是iif嵌套个数太多导致的，这个没办法，简化你的sql语句吧，只能这样。

我把代码长度减短也不行，

可能是

Dim SQL As String = "IIF(b.学期 Like '%秋%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"'- val(Left(b.学期,4))) *100 + val(班级)),IIF(b.学期 Like '%春%',IIF(('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级) < 1000,('"& s1 &"' - val(Left(b.学期,4))) *100 + val(班级)+100)))"

或下面组多了

Dim SQL1 As String = "IIF(Left(" & SQL & ",1) = 1,'一年级',IIF(Left(" & SQL & ",1) = 2,'二年级',IIF(Left(" & SQL & ",1) = 3,'三年级',IIF(Left(" & SQL & ",1) = 4,'四年级',IIF(Left(" & SQL & ",1) = 5,'五年级',IIF(Left(" & SQL & ",1) = 6,'六年级',IIF(Left(" & SQL & ",1) = 7,'七年级',IIF(Left(" & SQL & ",1) = 8,'八年级',IIF(Left(" & SQL & ",1) = 9,'九年级',null)))))))))"

iif嵌套不能过多，如果过多就会报错。

主题：表达式过于复杂