Form with Multiple Filters

Okay so when I press the button, get the error message, click OK and then go back to VB editor, in the Immediate Window it says : "[Repair]= True" (the quotations are not in the code)
 
Okay, so can you post the exact code you are now working with and I'll go through it again to see what might be set wrong?
 
Code: 
Private Sub QA_Report_Click()
    On Error GoTo Err_QA_Report_Click
    
    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnRepair As Boolean
    
        If Not IsNull(Me.Combo44) Then
            
            stWhere = "[Part]=" & Chr(34) & Me.Combo44 & Chr(34) & " AND "
        End If
        
        If Len(Me.Text22 & "") = 0 Then
            If Len(Me.Text24 & "") > 0 Then
                stWhere = stWhere & "[Cus Order Date] <=#" & Me.Text24 & "# AND "
            End If
        Else
            If Len(Me.Text24 & "") = 0 Then
                If Len(Me.Text22 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date]>=#" & Me.Text22 & "# AND "
                End If
            Else
                If Len(Me.Text22 & "") > 0 And Len(Me.Text24 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date] Between #" & Me.Text22 & "# And #" & Me.Text24 & "#"
                    blnRepair = False
                    End If
                End If
            End If
            
            If Me.Repair Then
               strWhere = strWhere & "[Repair]= True"
            End If
           
            If Right(strWhere, 5) = " AND " Then
                stWhere = Left(stWhere, Len(stWhere) - 5)
            End If

        stDocName = "QA Report"
        
        Debug.Print strWhere
        DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_QA_Report_Click:
    Exit Sub
Err_QA_Report_Click:
    MsgBox Err.Description
    Resume Exit_QA_Report_Click
End Sub
 
Okay, found a few errors (including a missing End If and strWhere instead of stWhere)

Code: 
Private Sub QA_Report_Click()
    On Error GoTo Err_QA_Report_Click
    
    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnRepair As Boolean
    
        If Not IsNull(Me.Combo44) Then
            
            stWhere = "[Part]=" & Chr(34) & Me.Combo44 & Chr(34) & " AND "
        End If
        
        If Len(Me.Text22 & "") = 0 Then
            If Len(Me.Text24 & "") > 0 Then
                stWhere = stWhere & "[Cus Order Date] <=#" & Me.Text24 & "# AND "
            End If
        Else
            If Len(Me.Text24 & "") = 0 Then
                If Len(Me.Text22 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date]>=#" & Me.Text22 & "# AND "
                End If
            Else
                If Len(Me.Text22 & "") > 0 And Len(Me.Text24 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date] Between #" & Me.Text22 & "# And #" & Me.Text24 & "#"
                End If  
            End If
        End If
            
            If Me.Repair Then
               stWhere = stWhere & "[Repair]= True"
            End If
           
            If Right(stWhere, 5) = " AND " Then
                stWhere = Left(stWhere, Len(stWhere) - 5)
            End If

        stDocName = "QA Report"
        
        Debug.Print strWhere
        DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_QA_Report_Click:
    Exit Sub
Err_QA_Report_Click:
    MsgBox Err.Description
    Resume Exit_QA_Report_Click
End Sub
 
You had the debug.print. strWhere


I changed it to debug.print.stWhere and did the same process. this time it came up with this in the Immediate Window:


[Part]="910-BT100A" AND [Cus Order Date] Between #Jun 01, 2010# And #Jul 08, 2010#[Repair]= True
 
Okay, one more try:

Code: 
Private Sub QA_Report_Click()
    On Error GoTo Err_QA_Report_Click
    
    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnRepair As Boolean
    
        If Not IsNull(Me.Combo44) Then
            
            stWhere = "[Part]=" & Chr(34) & Me.Combo44 & Chr(34) & " AND "
        End If
        
        If Len(Me.Text22 & "") = 0 Then
            If Len(Me.Text24 & "") > 0 Then
                stWhere = stWhere & "[Cus Order Date] <=#" & [COLOR="Red"]Format([/COLOR]Me.Text24[COLOR="red"],"mm/dd/yyyy")[/COLOR] & "# AND "
            End If
        Else
            If Len(Me.Text24 & "") = 0 Then
                If Len(Me.Text22 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date]>=#" & [COLOR="red"]Format([/COLOR]Me.Text22[COLOR="red"],"mm/dd/yyyy")[/COLOR] & "# AND "
                End If
            Else
                If Len(Me.Text22 & "") > 0 And Len(Me.Text24 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date] Between #" & [COLOR="red"]Format([/COLOR]Me.Text22[COLOR="red"],"mm/dd/yyyy")[/COLOR] & "# And #" & [COLOR="red"]Format([/COLOR]Me.Text24[COLOR="red"],"mm/dd/yyyy")[/COLOR] & "# [COLOR="Red"]AND[/COLOR] "
                End If  
            End If
        End If
            
            If Me.Repair Then
               stWhere = stWhere & "[Repair]= True"
            End If
           
            If Right(stWhere, 5) = " AND " Then
                stWhere = Left(stWhere, Len(stWhere) - 5)
            End If

        stDocName = "QA Report"
        
        Debug.Print strWhere
        DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_QA_Report_Click:
    Exit Sub
Err_QA_Report_Click:
    MsgBox Err.Description
    Resume Exit_QA_Report_Click
End Sub
 
Well, I don't get any error messages anymore, but when the report opens its completely blank with no records to show, even though the criteria states it should be showing certain records.
 
Okay, so post the Immediate Window stuff again for that one. And also, make sure that the FIELD names in the Report's Recordsource actually are named:

Part
Cus Order Date
Repair

and not something slightly different.
 
wait... I just played around a bit more, its ONLY bringing up records on the report where [Repair] is true, the checkbox doesn't seem to make a difference.
 
And is the Cus Order Date really a date field?
 
123dstreet said:
wait... I just played around a bit more, its ONLY bringing up records on the report where [Repair] is true, the checkbox doesn't seem to make a difference.
Click to expand...

What does the Immediate Window show if you don't check the checkbox?
 
There are very few records that are repaired, so when I left the date fields blank, and just selected a part number, it would show only those parts that have been repaired, no matter what the checkbox was.
 
When the box is UNCHECKED:

[Part]="910-BT100AVS" AND [Cus Order Date] <=#07 14 2010# AND [Repair]= True

When the box is CHECKED:

[Part]="910-BT100AVS" AND [Cus Order Date] <=#07 14 2010# AND [Repair]= True
 
Okay, try changing this:

If Me.Repair Then

to

If Me.Repair = True Then
 
I think I know what might be the problem, It's only returning records where that Part has been repaired. Whereas I want to be able to use the CheckBox in the form header to decide whether I want to view the parts repaired, or view the parts that haven't been repaired. The code is only telling it to return records that are Repaired from the query.
 
123dstreet said:
I think I know what might be the problem, It's only returning records where that Part has been repaired. Whereas I want to be able to use the CheckBox in the form header to decide whether I want to view the parts repaired, or view the parts that haven't been repaired. The code is only telling it to return records that are Repaired from the query.
Click to expand...

I think you confused me with this one. Are you saying that the report's query has some limiting going on as well? Not quite sure what you were saying is happening.
 
No there is no limit in the query. but it seems that the code for [Repairs] is only returning records where [Repairs] = True.

Doesn't there need to be an If statement to say something like:

if [this checkbox in the form header] = true then
return records where [Repair] = true Else
return all records (whether they were repaired or not)

?
 
That's what I did with:

boblarson said:
Change

If Me.Repair Then

to

If Me.Repair = True Then
Click to expand...

If working correctly it should not even add that line to the where clause if the checkbox is false.

So, maybe you should change it to this:

If Me.Repair <> 0 Then

instead of

If Me.Repair = True Then
 
I think I've figured most of it out except for one small thing:

Some of the Checkboxes for Repairs are Null, and some are FALSE, is there any way to have it so if "CheckRepair = False" that every record with a NULL or FALSE checkbox will be returned?


Code: 
Private Sub QA_Report_Click()
    On Error GoTo Err_QA_Report_Click
    
    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnRepair As Boolean
    
        If Not IsNull(Me.Combo44) Then
            
            stWhere = "[Part]=" & Chr(34) & Me.Combo44 & Chr(34) & " AND "
        End If
        
        If Len(Me.Text22 & "") = 0 Then
            If Len(Me.Text24 & "") > 0 Then
                stWhere = stWhere & "[Cus Order Date] <=#" & (Me.Text24) & "# AND "
            End If
        Else
            If Len(Me.Text24 & "") = 0 Then
                If Len(Me.Text22 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date]>=#" & (Me.Text22) & "# AND "
                End If
            Else
                If Len(Me.Text22 & "") > 0 And Len(Me.Text24 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date] Between #" & (Me.Text22) & "# And #" & (Me.Text24) & "# AND "
                End If
            End If
        End If
            
           [COLOR=red] If Me.CheckRepair = True Then
               stWhere = stWhere & "[Repair]= True"
            Else
                stWhere = stWhere & "IsNull([Repair]) = True "[/COLOR]
                  
                
            End If
           
            If Right(stWhere, 5) = " AND " Then
                stWhere = Left(stWhere, Len(stWhere) - 5)
            End If
 
        stDocName = "QA Report"
        
        Debug.Print stWhere
        DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_QA_Report_Click:
    Exit Sub
Err_QA_Report_Click:
    MsgBox Err.Description
    Resume Exit_QA_Report_Click
End Sub
 
You must log in or register to reply here.

Similar threads

A
Search box to find parts to add to job instead of a combo box
2 3
Replies
41
Views
1,615
Gasman
Gasman
MattBaldry
Solved Form Filter in VBA
Replies
15
Views
806
MattBaldry
MattBaldry
K
Filter with query
Replies
4
Views
491
Ken Sheridan
Ken Sheridan
C
Invalid use of Me.
Replies
8
Views
677
cheekybuddha
cheekybuddha
A
Filtering on a crosstab queried subform
Replies
7
Views
656
DHookom
DHookom

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom