[發問] [請教!!!]access查詢表單，核取方塊查詢

sandry49

下載 (79.29 KB)

2014-5-6 11:17

想做一個查詢，查詢介面如上圖，左方的條件vba程式已完成，
剩下右方的核取方塊查詢，我的核取方塊是依下方子報表的專案類別去做查詢
專案類別為多重值或是單一值，請問這樣要怎麼寫查詢語法

我目前的查詢語法為(寫在查詢按鈕的click事件)
Private Sub ProjectSearch_Click()
Dim stCri As String

If Me![SearchName] <> "" Then
        stCri = "專案名稱 like '*" & Me![SearchName] & "*'"
End If

If Me!S1 <> "" Or Me!S2 <> "" Then
    If stCri <> "" Then stCri = stCri & " and "
        stCri = stCri & "[開始時間] Between IIf(IsNull([FORMS]![專案查詢介面]![S1]),#1911/1/1#,[FORMS]![專案查詢介面]![S1]) And IIf(IsNull([FORMS]![專案查詢介面]![S2]),#2030/1/1#,[FORMS]![專案查詢介面]![S2])"
End If

If Me!E1 <> "" Or Me!E2 <> "" Then
    If stCri <> "" Then stCri = stCri & " and "
        stCri = stCri & "[結束時間] Between IIf(IsNull([FORMS]![專案查詢介面]![E1]),#1911/1/1#,[FORMS]![專案查詢介面]![E1]) And IIf(IsNull([FORMS]![專案查詢介面]![E2]),#2030/1/1#,[FORMS]![專案查詢介面]![E2])"
End If

If Me![SearchClient] <> "" Then
    If stCri <> "" Then stCri = stCri & " and "
       stCri = stCri & "公司名稱 like '*" & Me![SearchClient] & "*'"
End If

If Me![SearchPM] <> "" Then
    If stCri <> "" Then stCri = stCri & " and "
        stCri = stCri & "[PM] = '" & Me![SearchPM] & "'"
End If

If Me![SearchInCharge] <> "" Then
    If stCri <> "" Then stCri = stCri & " and "
        stCri = stCri & "[In Charge] = '" & Me![SearchInCharge] & "'"
End If


If stCri <> "" Then
Me.專案查詢子表單.Form.RecordSource = "SELECT * from 專案查詢 " & _
                                    "where " & stCri & _
                                    " order by 專案名稱"
Else
Me.專案查詢子表單.Form.RecordSource = "SELECT * from 專案查詢 " & _
                                    "order by 專案名稱"
End If

End Sub

mmxxxx

篩選條件不少, 可以利用Instr 或 Like 進行過濾篩選條逐一建立.  好像没有更簡單方式了.