Report Filter

Code: 
SELECT Students.[First Name], Students.[Last Name], Students.DOB, Students.Sex, Students.Ethnicity, Students.Diagnosis, Students.[Street 1], Students.County, Students.City, Students.State, Students.Zip, Students.[Resides With]
FROM Students
WHERE (((Students.[First Name])=[Forms]![Report Generator]![FName]))
ORDER BY Students.[Last Name];
 
Yep, that is the problem, try...

Code: 
SELECT Students.[First Name], Students.[Last Name], Students.DOB, Students.Sex, Students.Ethnicity, Students.Diagnosis, Students.[Street 1], Students.County, Students.City, Students.State, Students.Zip, Students.[Resides With]
FROM Students
WHERE (((Students.[First Name])=[Forms]![Report Generator]![FName].Column(1)))
ORDER BY Students.[Last Name];

OR

Code: 
SELECT Students.[First Name], Students.[Last Name], Students.DOB, Students.Sex, Students.Ethnicity, Students.Diagnosis, Students.[Street 1], Students.County, Students.City, Students.State, Students.Zip, Students.[Resides With]
FROM Students
WHERE (((Students.[ID])=[Forms]![Report Generator]![FName]))
ORDER BY Students.[Last Name];
 
:eek: Oops, forgot to tell you what the problem is...

Your Combo Box is looking at the ID
Your Report is looking at the First Name

They both need to look at the same field in order to work. So, Combo Box and Report either need to match on ID or First Name but they can't match ID to First Name as there is no match that way. Hope that makes sense.
 
Last edited:
Brilliant! That was definitely the problem. It works now. It makes sense to me too. I just couldn't figure out the problem on my own. Thank you for your help!

Now how do I update the report every time I press the button control on the form?
 
It should automatically match so you should see the report that match the name. If it does not then might have to change the syntax depending on whether you are using the ID or the First Name.
 
That's not exactly what I meant. I meant I want the report to requery without closing it or the form.

Also, how do I retrieve all of the records in the report by leaving the Combo Box blank?
 
Hmm, well, you could do something like...

Code: 
If Me.FName = "" Then
     DoCmd.OpenReport "Report", acViewReport
else
     DoCmd.OpenReport "Report", acViewReport, "[First Name]='" & Me.FName.Column(1) & "'"
End If

Not quite the way I would do it but it *should* work.
 
Never mind. I figured it out. Thank you so much for all of your friendly help! Have a wonderful day!
 
Even better, always great when you do it yourself! :D And... thanks for the kind words!
 
I hate to bother you again, but this is for everyone out there too. I've decided it would be better to take a different approach than queries. Instead of using a query, I want to filter the report using VBA and SQL. I found a tutorial here that takes this approach. However, I am having difficulties implementing it.

1. The Combo Boxes on the form contain an option for every unique Primary Key in the table, having multiple instances of the same name, for example. I only want one instance of each name.

2. The "Apply Filter" Command Button does not apply the filter correctly. Whenever any of the Combo Boxes or the Option Group do not have a value of Null, the report returns zero records.

I've messed around with it quite a bit, and I haven't really gotten anywhere.

Code: 
Private Sub ApplyFilter_Click()
    Dim strFName As String
    Dim strLName As String
    Dim strSex As String
    Dim strFilter As String

    If IsNull(Me.FName.Value) Then
        strFName = "Like '*'"
    Else
        strFName = "='" & Me.FName.Value & "'"
    End If

    If IsNull(Me.LName.Value) Then
        strLName = "Like '*'"
    Else
        strLName = "='" & Me.LName.Value & "'"
    End If

    Select Case Me.SexOptionGroup.Value
        Case 1
            strSex = "='M'"
        Case 2
            strSex = "='F'"
        Case 3
            strSex = "Like '*'"
    End Select

    strFilter = "[FName] " & strFName & " AND [LName] " & strLName & " AND [Sex] " & strSex

    With Reports![Students Report]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub
 
Right now I have it almost the way I want it. I can search by First Name and it will filter the report correctly. I can search by Last name and it will filter the report correctly. But I cannot search by both first and last name. It has something to do with AND. I have tried many variations of using ampersands and ANDs in the code, but there is always an error. My current code produces this error when I search by both first and last name: "Run-time error '3000': Reserved error (-3201); there is no message for this error." The Immediate window produces this: ([First Name] Like "*NAMEWITHHELD*")([Last Name] Like "*NAMEWITHHELD*"). There should be an AND between the two, but I can't figure out a way to do this without causing other errors.

Code: 
Private Sub ApplyFilter_Click()
    Dim strFName As String
    Dim strLName As String
    Dim strDiagnosis As String
    Dim strFilter As String
    Dim lngLen As Long

    If Not IsNull(Me.FName) Then
        strFName = "([First Name] Like ""*" & Me.FName & "*"")"
    End If
    
    If Not IsNull(Me.LName) Then
        strLName = "([Last Name] Like ""*" & Me.LName & "*"")"
    End If
    
    strFilter = [strFName] & [strLName]
    
    Debug.Print strFilter

    With Reports![Students Report]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub
 
Because you need the other part of the code...

See the AND at the end of the lines? Those combine the Filter. So...

Code: 
Private Sub ApplyFilter_Click()
    Dim strFName As String
    Dim strLName As String
    Dim strDiagnosis As String
    Dim strFilter As String
    Dim lngLen As Long
 
    If Not IsNull(Me.FName) Then
        strWhere = "([First Name] Like ""*" & Me.FName & "*"") AND "
    End If
 
    If Not IsNull(Me.LName) Then
        strWhere = "([Last Name] Like ""*" & Me.LName & "*"") AND"
    End If
 
'***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        Reports![Students Report].Filter = strWhere
        Reports![Students Report].FilterOn = True
    End If
 
End Sub

You can also modify to open report showing all the records if no selection is made.
 
I've already tried that method. I can search First Name with it, but I cannot search Last Name or first and last name. I get error: "Run-time error '3075': Missing ), ], or Item in query expression '([Last Name] Like "*NAMEWITHHELD*"'." and it highlights "Reports![Students Report].Filter = strFilter" in the debugger. The Immediate window produces: ([Last Name] Like "*Cook*") AND

Code: 
Private Sub ApplyFilter_Click()
    Dim strFName As String
    Dim strLName As String
    Dim strFilter As String
    Dim lngLen As Long

    If Not IsNull(Me.FName) Then
        strFilter = "([First Name] Like ""*" & Me.FName & "*"") AND "
    End If
    
    If Not IsNull(Me.LName) Then
        strFilter = "([Last Name] Like ""*" & Me.LName & "*"") AND"
    End If
    
    lngLen = Len(strFilter) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strFilter = Left$(strFilter, lngLen)
        Reports![Students Report].Filter = strFilter
        Reports![Students Report].FilterOn = True
    End If
End Sub
 
It appears not to be stripping off the last AND and could be you have an OrderBy included in the Record Source of your Report? Hmm, please post the SQL which is the Record Source of your Report.
 
I tried removing all ordering functions in all elements of the database, but the Record Source of the Report is the primary table.
 
I see a space missing, maybe that's it...

Code: 
strFilter = "([Last Name] Like ""*" & Me.LName & "*"") AND "

Side note: I do this all the time so I know it's something simple, we're just not seeing it!
 
BAM! That's it! I didn't know it was supposed to be like that. Thank you! :D
 
You must log in or register to reply here.

Similar threads

T
Report in unbound Form needs dynamic Recordsource and Filter
Replies
15
Views
589
sonic8
S
F
Solved Mask with sub-mask filtered by 2 combined drop-down boxes and a checkbox, opening two different reports depending on the checkbox
Replies
6
Views
607
Faoineag
F
A
Solved Filter Report
Replies
15
Views
704
adamsro
A
D
Error on cancel
Replies
9
Views
618
Pat Hartman
P
D
Solved Open a report that filters off a form field
Replies
7
Views
842
dullster
D

Users who are viewing this thread

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