Question

VB中sql语句用法的问题。

sql = "select * from NS where"
Adodc1.CommandType = adCmdText

If Check15.Value = 1 Then
If Option6.Value = True Then
gzlh_opt1 = " and (gzlh='" & Text3(0).Text & "')"
sql = sql + gzlh_opt1
ElseIf Option11.Value = True Then
If Mid$(Text3(1).Text, 1, 1) > "0" And Mid$(Text3(1).Text, 1, 1) <= "9" And Mid$(Text3(2).Text, 1, 1) > "0" And Mid$(Text3(2).Text, 1, 1) <= "9" Then
gzlh_opt2 = " val(gzlh) between " & Val(Text3(1).Text) & " and " & Val(Text3(2).Text)
sql = sql + gzlh_opt2
Else
gzlh_opt2 =" and (mid$(gzlh,1,1)="B") and Val(mid$(gzlh,2,7)) between " & Val(Mid$(Text3(1).Text, 2, 7)) & " and " & Val(Mid$(Text3(2).Text, 2, 7))
sql = sql + gzlh_opt2
End If
End If
End If

Adodc1.RecordSource = sql

Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
DataGrid1.Refresh

问题： 执行第一条SQL语句是没有问题的。但是第二条出错，格式错误：
gzlh_opt2 =" and (mid$(gzlh,1,1)="B") and Val(mid$(gzlh,2,7)) between " & Val(Mid$(Text3(1).Text, 2, 7)) & " and " & Val(Mid$(Text3(2).Text, 2, 7))

想请问大家知道哪里错了么？我想查gzlh这个字段 首字母为B，然后范围在text3（1）和text3(2)之间的数据。

Answer 1

双引号标错，查询条件“B”要单独引号。试试这样
gzlh_opt2 =" and (mid$(gzlh,1,1)=" & "B" & ") and Val(mid$(gzlh,2,7)) between " & Val(Mid$(Text3(1).Text, 2, 7)) & " and " & Val(Mid$(Text3(2).Text, 2, 7))
（未运行）

追问

No..不行的~~报错：至少一个参数没有指定值

Answer 2

val，mid$ 在SQL中有这些函数吗？应该修改成Convert和SubString来用才比较合适。Sql中引用字符串或者字符必须单引号括起来：‘B’。变量加入Sql问也一样。