我在Access中可以通过的这条SQL语句，怎么在VB中就通不过了呢？

[linzaixuan001]

SELECT hou.名 AS 员工, hou.数-IIf(IsNull(qian.数),0,qian.数) AS 工资 FROM hou LEFT JOIN qian ON hou.名 = qian.名;


上面这条语句，在Access数据库查询中可以通过，然后我把它照搬到这里rs.Open "SELECT hou.名 AS 员工, hou.数-IIf(IsNull(qian.数),0,qian.数) AS 工资 FROM hou LEFT JOIN qian ON hou.名 = qian.名;", conn, adOpenKeyset, adLockOptimistic

运行时就出错了，错误就指向这条SQL语句，提示信息是：至少有一个参数没有被指定


VB中连接的也是Access数据库，难道VB中和Access的SQL语句不一样？请高手改改，感激不尽！

[threenewbee]

去掉分号。

[饮水需思源]

rs.Open "SELECT hou.名 AS 员工, hou.数-IIf(IsNull(qian.数),0,qian.数) AS 工资 FROM hou LEFT JOIN qian ON hou.名 = qian.名", conn, adOpenKeyset, adLockOptimistic

[linzaixuan001]

两位大哥所说的都不行啊

[vbman2003]

try:

rs.Open "SELECT hou.名 AS 员工, hou.数-IIf(format(qian.数)='',0,qian.数) AS 工资 FROM hou LEFT JOIN qian ON hou.名 = qian.名", conn, adOpenKeyset, adLockOptimistic 

jet应该不支持isnull

[linzaixuan001]

[Quote=引用 4 楼 vbman2003 的回复:]
try:
VB code
rs.Open"SELECT hou.名 AS 员工, hou.数-IIf(format(qian.数)='',0,qian.数) AS 工资 FROM hou LEFT JOIN qian ON hou.名 = qian.名", conn, adOpenKeyset, adLockOptimistic
jet应该不支持isnull
[/Quote]

还是不行，错误提示和原来一样

isnull和in null 一样吗，我之前有个VB代码支持in null

[linzaixuan001]

写错了，是和 is null

[jhone99]

try this

rs.Open "SELECT hou.名 AS 员工, hou.数-IIf((qian.数) is null,0,qian.数) AS 工资 FROM hou LEFT JOIN qian ON hou.名 = qian.名", conn, adOpenKeyset, adLockOptimistic 

[linzaixuan001]

还是不行，我把那串代码直接用"select * from qian"或"select * from hou"代替，可以正确显示出内容，说明连接和其它代码没有问题，问题就出在那串SQL语句上

[slowgrace]

你的rs是怎么声明的？

[linzaixuan001]

前面是这样写的：


Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\mydb.mdb;" & "Persist Security Info=False"
conn.Open

[vbman2003]

要看下二个表的结构...

[linzaixuan001]

[Quote=引用 11 楼 vbman2003 的回复:]
要看下二个表的结构...
[/Quote]

两个表都只有两个字段，一个字段叫“名”，文本型；另一个叫“数”，也是文本型。　会不会因为文本型而不行呢

[jhone99]

try this

rs.Open "SELECT hou.名 AS 员工, hou.数-IIf((qian.数) & '' = ''  ,0,qian.数) AS 工资 FROM hou LEFT JOIN qian ON hou.名 = qian.名", conn, adOpenKeyset, adLockOptimistic 

[slowgrace]

两个表的字段[数]都不要用文本型，改成长整型，再试。

[linzaixuan001]

各位大哥，真是万分对不起啊，是表字段和SQL字段不符的问题，小弟7改8改，把字段改错了，由于我的粗心，浪费大家这么多时间，真的很抱歉

我又试了下，4、7、13楼的大哥的语句都是可行的

再次谢谢学长们的热心帮助