Question

VB 数据类型及转换问题。

sqlstring = "select FNumber from vwICBill_2 where Year(fdate)=" & [b3] & " and Month(fdate) =" & [b4] & " and left(fNumber,8)=" & [j17]
worksheets(1).[a51].copyFromRecordset conn.Execute(sqlstring)

上面代码存在数据转换问题。。。请高手教教如何写。。。
FNumber是个string。。。是物料代码。。。一般04.02.2235.1110之类的。。想取前8位04.02.22来识别物料种类。。。。可惜总是错。。。求教…………
有些简单的物料代码。。。我取前5位就可识别。比如04.22。。就没有问题。。。
当些复杂的物料代码。。。要取前8位。。就出问题了。。也想过right(left(fumber,8),5)。。来取中间的02.22。。和取8位一样。。错误。。
~
~
2013-04-24 9:30更新……
问题在于：
left(fumber,5) 没问题。。。可以正常运行。。
left(fumber,8)就……
运行时错误 '-2147217900 (80040e14)';
列名'FNumber'无效。

Answer 1

sqlstring = "select FNumber from vwICBill_2 where Year(fdate)=" & [b3] & " and Month(fdate) =" & [b4] & " and left(fNumber,8)=" & [j17]
应改为：
sqlstring = "select FNumber from vwICBill_2 where Year(fdate)=" & [b3] & " and Month(fdate) =" & [b4] & " and left(fNumber,8)=‘" & [j17] &"'"

fnumber为字符串类型，其比较数据应用引号括起来。

追问

还是没用……

Answer 2

sqlstring = "select FNumber from vwICBill_2 where Year(fdate)=" & [b3] & " and Month(fdate) =" & [b4] & " and left(fNumber,8)=" & [j17]
SQL语句有问题：
1、只能读取 FNumber 字段
2、Where 后面的字段名没有
3、Year(fdate)=" & [b3] 、" and Month(fdate) =" & [b4] 、 " and left(fNumber,8)=" & [j17] 中的fdate、b3、b4、j17？？
4、 " and left(fNumber,8)=" & [j17] 是字符串，必须是： " and left(fNumber,8)='" & [j17] 注意必须有单引号。
5、 " and left(fNumber,8)='" & [j17] 取前五位应该是： " and left(fNumber,5)='" & [j17]

Answer 3

我觉得你把VB语句和SQL语句混在一起了，试试这样行不行：
sqlstring = "select FNumber from vwICBill_2 where " & Year(fdate) & "=" & [b3] & " and " & Month(fdate) & "=" & [b4] & " and " & Left(fNumber, 8) & "=" & [j17]

Answer 4

试试以下语句
sqlstring = "select FNumber from vwICBill_2 where Year(fdate)=" & [b3] & " and Month(fdate) =" & [b4] & " and FNumber like '%" & [j17] & "%'"
worksheets(1).[a51].copyFromRecordset conn.Execute(sqlstring)

追问

依旧
运行时错误 '-2147217900 (80040e14)';
列名'FNumber'无效

Answer 5

问题可能出在别的地方吧