[求助]照猫没画成虎，请教！（关于Form.RecordSource =）

itez

现在就原有ACCESS程序添加功能，其中一个搜索功能想要加入一些限制条件，例如：hiding字段的值>5或者<1，改了半天还是报错，特此向各位大侠请教，谢谢！

下面是部分程序，请指教如何在原基础之上加入条件“hiding字段的值>5或者<1”。

Private Sub search_button_Click()
    On Error GoTo Err_search_button_Click
    Dim stLinkCriteria As String
    Beep
    Data = "'" & keydata & "*'"
    Select Case flame1
        Case 1
            If check1.Value = True Then
                Data = "'*" & keydata & "*'"
            End If
            Mysql = "SELECT * FROM PartNumber_Master WHERE specification LIKE" & Data & "ORDER BY specification ASC"
        Case 2
            Mysql = "SELECT * FROM PartNumber_Master WHERE partnumber LIKE" & Data & "ORDER BY partnumber ASC"
        Case 3
            Data = "'*" & keydata & "*'"
            Mysql = "SELECT * FROM PartNumber_Master WHERE machine_number LIKE" & Data & "ORDER BY partnumber ASC"
        Case 4
            Data = "'*" & keydata & "*'"
            Mysql = "SELECT * FROM PartNumber_Master WHERE description1 LIKE" & Data & "ORDER BY partnumber ASC"
        Case 5
            Data = "'*" & keydata & "*'"
            Mysql = "SELECT * FROM PartNumber_Master WHERE description2 LIKE" & Data & "ORDER BY partnumber ASC"

        Case 6
            Data = "'*" & keydata & "*'"
            Mysql = "SELECT * FROM PartNumber_Master WHERE supplier LIKE" & Data & "ORDER BY partnumber ASC"
        Case 7
            DoCmd.Close
            DoCmd.OpenForm "InventoryQty_Search", , , stLinkCriteria
            Exit Sub
        Case Else
            Title = Title01
            Msg = Msg1_05
            Style = vbOKOnly + vbExclamation
            Response = MsgBox(Msg, Style, Title)
            Exit Sub
    End Select
    '
    Forms![Data_Search]![subform].Form.RecordSource = Mysql
    Set Tb1 = Db1.OpenRecordset(Mysql, dbOpenDynaset)
    Datakensuu = Tb1.RecordCount
    Tb1.Close
    Select Case Datakensuu
        Case 0
            subform.Visible = False
            msgguide = Msg3_04
        Case Else
            subform.Visible = True
            excel_button.Visible = True
            msgguide = Msg3_05
    End Select
    keydata.SetFocus
Exit_search_button_Click:
    Exit Sub
Err_search_button_Click:
    Call Error_Syori
    GoTo Exit_search_button_Click
End Sub

andymark

第一种方法是在每种CASE 下加 条件hiding字段的值>5或者<1

第二种方法是在End Select 后重新更改 Mysql 的语句,可以用REPLACE把条件给ODER BY 替换成 and (hiding >5 or hiding <1 ) order by

itez

谢谢版主的回复!

我把

Case 4
            Data = "'*" & keydata & "*'"
            Mysql = "SELECT * FROM PartNumber_Master WHERE description1 LIKE" & Data & "ORDER BY partnumber ASC"



修改成

Case 4
            Data = "'*" & keydata & "*'"
            Mysql = "SELECT * FROM PartNumber_Master WHERE description1 LIKE" & Data & "and (hiding >5 or hiding <1 ) order by partnumber ASC"

还是报错呢?