[已解决]FROM子句语法错误

hvail

FROM子句语法错误

最佳答案

月排行榜 / 总排行榜

mxg825

2012-8-31 11:49

strsql = "select * from 核算表 WHERE "
If lx <> "" Then
strsql = strsql & "产品类型 like '" & lx & "'and "
End If
If xm <> "" Then
strsql = strsql & "项目号 like '" & xm & "'and "
End If
If ht <> "" Then
strsql = strsql & "合同号 like '" & ht & "'and "
End If
If kh <> "" Then
strsql = strsql & "客户 like '" & kh & "'and "
End If
If gys <> "" Then
strsql = strsql & "供货商 like '" & gys & "'and "
End If
If xh <> "" Then
strsql = strsql & "型号 like '" & xh & "'and "
End If
l = Len(strsql)
strsql = Left(strsql, l - 4)

跳转到最佳答案楼层

mxg825

这个问题是VBA 问题，请发到VBA版块！

hvail

老师帮我改改嘛！

mxg825

strsql = "select * from 核算表 WHERE "
If lx <> "" Then
strsql = strsql & "产品类型 like '" & lx & "'and "
End If
If xm <> "" Then
strsql = strsql & "项目号 like '" & xm & "'and "
End If
If ht <> "" Then
strsql = strsql & "合同号 like '" & ht & "'and "
End If
If kh <> "" Then
strsql = strsql & "客户 like '" & kh & "'and "
End If
If gys <> "" Then
strsql = strsql & "供货商 like '" & gys & "'and "
End If
If xh <> "" Then
strsql = strsql & "型号 like '" & xh & "'and "
End If
l = Len(strsql)
strsql = Left(strsql, l - 4)

mxg825

注意你的空格 AND 等等，字符数，
在
strsql = Left(strsql, l - 4) ‘下面加一句
MsgBox strsql’看一下组合结果，是否正确！

mxg825

帮你优化一下 IF 过程（做成一个子程序）

1. Private Sub CommandButton1_Click()
   
2. Dim cnn As New ADODB.Connection
   
3. Dim rst As New ADODB.Recordset
   
4. Dim xm As String, ht As String, kh As String
   
5. Dim gys As String, lx As String, xh As String
   
6. Dim stpath As String, strsql As String
   
7. Dim l As Integer, n As Integer
   
8. stpath = ThisWorkbook.Path & Application.PathSeparator & "fish.mdb"
   
9. cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & stpath
   
10. xm = ComboBox1.Text
    
11. ht = ComboBox2.Text
    
12. lx = ComboBox3.Text
    
13. xh = ComboBox4.Text
    
14. kh = ComboBox5.Text
    
15. gys = ComboBox6.Text
    
16.     strsql = "select * from 核算表 WHERE "
    
17.     strsql = strsql & sqlAnd("产品类型", lx) '调用子程序组合
    
18.     strsql = strsql & sqlAnd("项目号", xm)
    
19.     strsql = strsql & sqlAnd("合同号", ht)
    
20.     strsql = strsql & sqlAnd("客户", kh)
    
21.     strsql = strsql & sqlAnd("供货商", gys)
    
22.     strsql = strsql & sqlAnd("型号", xh)
    
23. l = Len(strsql)
    
24. strsql = Left(strsql, l - 5)
    
25. MsgBox strsql
    
26. rst.Open strsql, cnn
    
27. n = Sheet2.Range("B65536").End(xlUp).Row
    
28. Sheet2.Rows("5:" & n).ClearContents
    
29. Sheet2.[B5].CopyFromRecordset rst
    
30. rst.Close
    
31. cnn.Close
    
32. Set TST = Nothing
    
33. End Sub
    
34. Function sqlAnd(fieldName As String, Tet As String) As String
    
35. If Len(Tet) > 0 Then
    
36.     sqlADD = fieldName & " Like '" & Tet & "' and "
    
37. End If
    
38. End Function

复制代码

帮你优化一下 IF 过程（做成一个子程序）

zbhpass

Private Sub CommandButton1_Click()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim xm As String, ht As String, kh As String
    Dim gys As String, lx As String, xh As String
    Dim stpath As String, strsql As String
    Dim l As Integer, n As Integer
    stpath = ThisWorkbook.Path & Application.PathSeparator & "fish.mdb"
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & stpath
    xm = ComboBox1.Text
    ht = ComboBox2.Text
    lx = ComboBox3.Text
    xh = ComboBox4.Text
    kh = ComboBox5.Text
    gys = ComboBox6.Text
    strsql = "select * from 核算表 WHERE"
    If lx <> "" Then
        strsql = strsql & " 产品类型 like """ & lx & """ and "
    End If
    If xm <> "" Then
        strsql = strsql & " 项目号 LIKE """ & xm & """ and"
    End If
    If ht <> "" Then
        strsql = strsql & " 合同号 LIKE """ & ht & """ and"
    End If
    If kh <> "" Then
        strsql = strsql & " 客户 LIKE """ & kh & """ and"
    End If
    If gys <> "" Then
        strsql = strsql & " 供货商 LIKE """ & gys & """ and"
    End If
    If xh <> "" Then
        strsql = strsql & " (型号 LIKE """ & xh & """)"
    End If
    'l = Len(strsql)
    'strsql = Left(strsql, l - 5)
    rst.Open strsql, cnn
    n = Sheet2.Range("B65536").End(xlUp).Row
    Sheet2.Rows("5:" & n).ClearContents
    Sheet2.[B5].CopyFromRecordset rst
    rst.Close
    cnn.Close
    Set TST = Nothing
End Sub