tongnaifu :你好,我们一起分析,结果如下,谢谢select a.modelname, iif(a.moduleclassify='',a.modelname,b.modulename) as module0, iif(b.moduleclassify='',a.channelspermodule,b.channelspresent) as lendchannel0, iif(b.channelsoccupy = 0, a.channelspermodule, b.channelsoccupy) As occupyChannel0 from instrumentavailable a left join moduleavailable b on a.moduleclassify = b.moduleclassify order by a.modelname
tongnaifu :你好,我们一起分析,结果如下,谢谢select a.modelname, iif(a.moduleclassify='',a.modelname,b.modulename) as module0, iif(b.moduleclassify='',a.channelspermodule,b.channelspresent) as lendchannel0, iif(b.channelsoccupy = 0, a.channelspermodule, b.channelsoccupy) As occupyChannel0 from instrumentavailable a left join moduleavailable b on a.moduleclassify = b.moduleclassify order by a.modelname
debug.print strSql 在立即窗口和查询分析中调试你的语句....
看了一下你的这个语句,好像是issqlserver=false时候生成的,如果不是SQL server数据库,那你连接的是什么数据库?而且在SQL server 里面是不支持iif语句的吧
楼上说的有道理,SQL数据库不支持iif,Access不支持case...
我的是 SQL server数据库 我不太清楚的,那我该怎么解决呢,谢谢!
那你写那个issqlserver函数的判断是干吗用的?把那个函数也贴出吧
谢谢各位,问题解决了,就是 SQL server数据库 不支持iif()函数,我把它换成了 SqlStr = "select a.modelname," _ & "(case isnull(a.moduleclassify,'') when '' then a.modelname else b.modulename end) as module0, " _ & "(case isnull(b.moduleclassify,'') when '' then a.channelspermodule else b.channelspresent end) as lendchannel0, " _ & "(case isnull(b.channelsoccupy,'') when '' then a.channelspermodule else b.channelsoccupy end) as occupyChannel0 " _ & "from instrumentavailable a,moduleavailable b " _ & "where a.moduleclassify*=b.moduleclassify order by a.modelname"就ok了,谢谢大家请接分!!!!!!
'初始化模组模块对应关系ModelModuleArr
Dim tRs As New ADODB.Recordset, SqlStr$
If IsSqlServer = True Then
SqlStr = "select a.modelname," _
& "(case isnull(a.moduleclassify,'') when '' then a.modelname else b.modulename end) as module0, " _
& "(case isnull(b.moduleclassify,'') when '' then a.channelspermodule else b.channelspresent end) as lendchannel0, " _
& "(case isnull(b.channelsoccupy,'') when '' then a.channelspermodule else b.channelsoccupy end) as occupyChannel0 " _
& "from instrumentavailable a,moduleavailable b " _
& "where a.moduleclassify*=b.moduleclassify order by a.modelname"
Else
SqlStr = "select a.modelname," _
& " iif(a.moduleclassify='',a.modelname,b.modulename) as module0, " _
& " iif(b.moduleclassify='',a.channelspermodule,b.channelspresent) as lendchannel0, " _
& " iif(b.channelsoccupy = 0, a.channelspermodule, b.channelsoccupy) As occupyChannel0 " _
& " from instrumentavailable a left join moduleavailable b on" _
& " a.moduleclassify = b.moduleclassify " _
& " order by a.modelname "
End If
'
tRs.CursorLocation = adUseClient
tRs.Open SqlStr, ConSys, adOpenDynamic
ModelModuleArr.MaxId = 0
Dim UpModelName$
''
UpModelName = "XX"
Do While Not tRs.EOF
If UpModelName <> tRs!ModelName Then
ModelModuleArr.MaxId = ModelModuleArr.MaxId + 1
ModelModuleArr.Arr(ModelModuleArr.MaxId).ModelName = tRs!ModelName
ModelModuleArr.Arr(ModelModuleArr.MaxId).ModuleName = IIf(IsNull(tRs!module0), tRs!ModelName, tRs!module0)
ModelModuleArr.Arr(ModelModuleArr.MaxId).LendChannel = IIf(IsNull(tRs!LendChannel0), 1, tRs!LendChannel0)
ModelModuleArr.Arr(ModelModuleArr.MaxId).occupyChannel = IIf(IsNull(tRs!occupyChannel0), 1, tRs!occupyChannel0)
UpModelName = IIf(IsNull(tRs!module0), tRs!ModelName, tRs!module0)
End If
'
tRs.MoveNext
Loop
End Sub运行到这里
tRs.Open SqlStr, ConSys, adOpenDynamic
提示:
实时错误 ‘-2147217900(80040e14)’:
第1行:‘=’附近有语法错误。 谢谢
& "where a.moduleclassify*=b.moduleclassify order by a.modelname" 我对SQL不是很了解,是不是 多了一个 * ?
这里多打了一个*吧
tongnaifu :你好,我们一起分析,结果如下,谢谢select a.modelname, iif(a.moduleclassify='',a.modelname,b.modulename) as module0, iif(b.moduleclassify='',a.channelspermodule,b.channelspresent) as lendchannel0, iif(b.channelsoccupy = 0, a.channelspermodule, b.channelsoccupy) As occupyChannel0 from instrumentavailable a left join moduleavailable b on a.moduleclassify = b.moduleclassify order by a.modelname
tongnaifu :你好,我们一起分析,结果如下,谢谢select a.modelname, iif(a.moduleclassify='',a.modelname,b.modulename) as module0, iif(b.moduleclassify='',a.channelspermodule,b.channelspresent) as lendchannel0, iif(b.channelsoccupy = 0, a.channelspermodule, b.channelsoccupy) As occupyChannel0 from instrumentavailable a left join moduleavailable b on a.moduleclassify = b.moduleclassify order by a.modelname
在立即窗口和查询分析中调试你的语句....
我不太清楚的,那我该怎么解决呢,谢谢!
SqlStr = "select a.modelname," _
& "(case isnull(a.moduleclassify,'') when '' then a.modelname else b.modulename end) as module0, " _
& "(case isnull(b.moduleclassify,'') when '' then a.channelspermodule else b.channelspresent end) as lendchannel0, " _
& "(case isnull(b.channelsoccupy,'') when '' then a.channelspermodule else b.channelsoccupy end) as occupyChannel0 " _
& "from instrumentavailable a,moduleavailable b " _
& "where a.moduleclassify*=b.moduleclassify order by a.modelname"就ok了,谢谢大家请接分!!!!!!