Filtering problem with SQL and VBA 1

[ColdDay]

I keep getting an error message stating that I have written “a subquery that can return more than one field without using the EXISTS reserved word in the main query’s FROM clause. Revise the SELECT statement of the subquery to request only one field.”

But I am needing all of the fields to appear on the report based upon the filtering criteria of all the fields in the VBA “strFilter” query below. Following is all the code for the procedure.

Option Compare Database
Option Explicit

Private Sub cmd_ApplyFilter_Click()
'
'variables for holding the SQL code from the text boxes
Dim strBeginDate As String
Dim strEndDate As String

'variables for holding the SQL code from the combo boxes
Dim strLocation As String
Dim strReportedBy As String
Dim strStaffInvolved As String
Dim strCategory As String
Dim strSubCategory As String

'variable to hold the combined criteria for the filter
Dim strFilter As String

'open report code here
DoCmd.OpenReport "rpt_DetailReportBySelection", acViewPreview
'
'if a combo box is empty its value is NULL. using an IF statement to check _
    whether or not the user made a choice and then create the appropriate _
    SQL code
    
'code for empty beginning date text box
If IsNull(Me.txt_BeginDate.Value) Then
    strBeginDate = "Like'*'"
Else 'Note the use of the single quote marks above and below
    strBeginDate = "='" & Me.txt_BeginDate.Value & "'"
End If

'code for empty ending date text box
If IsNull(Me.txt_EndDate.Value) Then
    strEndDate = "Like'*'"
Else 'Note the use of the single quote marks above and below
    strEndDate = "='" & Me.txt_EndDate.Value & "'"
End If

'code for empty Location combo box
If IsNull(Me.cmb_Location.Value) Then
    strLocation = "Like'*'"
Else 'Note the use of the single quote marks above and below
    strLocation = "='" & Me.cmb_Location.Value & "'"
End If

'code for empty ReportBy combo box
If IsNull(Me.cmb_ReportedBy.Value) Then
    strReportedBy = "Like'*'"
Else 'Note the use of the single quote marks above and below
    strReportedBy = "='" & Me.cmb_ReportedBy.Value & "'"
End If

'code for empty StaffInvolved combo box
If IsNull(Me.cmb_StaffInvolved.Value) Then
    strStaffInvolved = "Like'*'"
Else 'Note the use of the single quote marks above and below
    strStaffInvolved = "='" & Me.cmb_StaffInvolved.Value & "'"
End If

'code for empty Category combo box
If IsNull(Me.cmb_Category.Value) Then
    strCategory = "Like'*'"
Else 'Note the use of the single quote marks above and below
    strCategory = "='" & Me.cmb_Category.Value & "'"
End If

'code for empty SubCategory combo box
If IsNull(Me.cmb_SubCategory.Value) Then
    strSubCategory = "Like'*'"
Else 'Note the use of the single quote marks above and below
    strSubCategory = "='" & Me.cmb_SubCategory.Value & "'"
End If

strFilter = " SELECT Location, ReportedBy, StaffInvolved, Category, SubCategory" & _
            " FROM tbl_DisruptionDetails " & _
            " WHERE ReportDate BETWEEN [BeginDate] " & strBeginDate & _
            " AND [EndDate] " & strEndDate & _
            " AND [ReportedBy] " & strReportedBy & _
            " AND [Location] " & strLocation & _
            " AND [StaffInvolved] " & strStaffInvolved & _
            " AND [Category] " & strCategory & _
            " AND [SubCategory] " & strSubCategory

'filter is applied to the report and switched on
With Reports![rpt_DetailReportBySelection]
    .Filter = strFilter
    .FilterOn = True
End With

End Sub

I have tried both a table and query as the record source for the form that has the text and combo boxes. Here is the query:

SELECT tbl_DisruptionDetails.*, *
FROM tbl_DisruptionDetails
WHERE (((tbl_DisruptionDetails.ReportDate) Between [Forms]![zzzfrm_prm_DetailsFiltered]![txt_BeginDate] And [Forms]![zzzfrm_prm_DetailsFiltered]![txt_EndDate]));

I have also tried the following but keep getting an Expected End of Statement error with the parenthesis on the last line after strSubCategory being highlighted.

strFilter = " SELECT Location, ReportedBy, StaffInvolved, Category, SubCategory" & _
            " FROM tbl_DisruptionDetails " & _
" WHERE EXISTS (SELECT ReportDate BETWEEN [BeginDate] " & _ “strBeginDate “ & _
            " AND [EndDate] " & strEndDate & _
            " AND [ReportedBy] " & strReportedBy & _
            " AND [Location] " & strLocation & _
            " AND [StaffInvolved] " & strStaffInvolved & _
            " AND [Category] " & strCategory & _
            " AND [SubCategory] " & strSubCategory)

If I put a semi-colon between the parenthesis and strSubCategory, I again get an Expected End of Statement error.

Thanks in advance.

 

[TheAceMan1]

How are ya ColdDay . . .

Not only is your syntax all wrong but:

[blue]Filter Property: [purple]The Filter property is a string expression consisting of a WHERE clause without the WHERE keyword.[/purple] For example, the following Visual Basic code defines and applies a filter to show only customers from the USA:

Me.Filter = "Country = 'USA'"
Me.FilterOn = True[/blue]

Your constructing a full [blue]Select[/blue] statement instead of a [blue]Where[/blue] clause without the Where.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

My code would look something like this:

Private Sub cmd_ApplyFilter_Click()
'

'variable to hold the combined criteria for the filter
Dim strFilter As String
strFilter = "1 = 1 "

'if a combo box is empty its value is NULL. using an IF statement to check _
    whether or not the user made a choice and then create the appropriate _
    SQL code
    
'code for empty beginning date text box
If Not IsNull(Me.txt_BeginDate) Then
    strFilter = strFilter & " AND [DateField] >=#" & Me.txt_BeginDate & "# "
End If

'code for empty ending date text box
If Not IsNull(Me.txt_EndDate) Then
    strFilter = strFilter & " AND [DateField] <=#" & Me.txt_EndDate & "# "
End If

'code for empty Location combo box
If Not IsNull(Me.cmb_Location) Then
    strFilter = strFilter & " AND [Location] = '" & Me.cmb_Location & "' "
End If

'code for empty ReportBy combo box
If Not IsNull(Me.cmb_ReportedBy) Then
    strFilter = strFilter & " AND [ReportedBy] = '" & Me.cmb_ReportedBy & "' "
End If

'code for empty StaffInvolved combo box
If Not IsNull(Me.cmb_StaffInvolved) Then
    strFilter = strFilter & " AND [StaffInvolved] = '" & Me.cmb_StaffInvolved & "' "
End If

'code for empty Category combo box
If Not IsNull(Me.cmb_Category) Then
    strFilter = strFilter & " AND [Category] ='" & Me.cmb_Category & "' "
End If

'code for empty SubCategory combo box
If Not IsNull(Me.cmb_SubCategory) Then
    strFilter = strFilter & " AND [SubCategory]='" & Me.cmb_SubCategory & "' "
End If

'open report code here
DoCmd.OpenReport "rpt_DetailReportBySelection", acViewPreview, , strFilter
'

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[ColdDay]

Duane,

The code works as desired with one exception (that I've found so far) and that is that each date field will not hold its own date. The first date field changes its date to the date selected in the second field and vice-versa.

In other words, which ever field is filled in first is changed to the value of the field filled in second. This happens when I click the ApplyFilter click event or when I click into one of the combo boxes.

I am using the date picker, though I don't think that this matters.

Is there a "persistence" type of choice for the field property? I looked but could not find any?

Plus, I do not understand the strFilter = "1 = 1 " line. To test it, I tried a variety of numbers. I changed it to "9 = 9 " then "19 = 19 " and it worked, but when I did "1 = 19 " it did not return any records. Obviously the numbers have to be the same, but what is it doing?

Thanks for the help.

AceMan,

It sounds like I am confusing "filtering" with "selecting".

Thanks for the insight.

 

[dhookom]

I am confused by your date filtering and what's happening.

The "1=1" is just any expression that evaluates to true. It has no real function except to allow adding more " AND ...." criteria without testing.

Duane
Hook'D on Access
MS Access MVP

 

[ColdDay]

I figured it out. Partially at least.

Each date text box had as its control source “ReportDate”. When I changed both boxes to UnBound, then the problem went away. But now I no longer have the date picker calendar to use.

Any way I can keep the calendar?

Thanks.

 

[dhookom]

Try set the format property to short date.

Duane
Hook'D on Access
MS Access MVP

 

[ColdDay]

Got it. I had set an input mask. I removed it and now it works as desired.

Thanks for the help!!