Report "Filtering" with User entered Data

[Anneybo]

Alright, I give up! I'm asking the experts. I have created a database that calculates PTO for employees. I need to be able to cache the report by user entered dates and specific employee names. I have been able to get the dates to work with the following VBA script:

..
Private Sub cmdOK_Click()
 
Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"
 
    strReport = "PTO Form"
    strField = "DateUsed"
 
    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
            strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
        End If
    Else
        If IsNull(Me.txtEndDate) Then       'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
                & " And " & Format(Me.txtEndDate, conDateFormat)
        End If
 
    End If
 
 
 
    ' Debug.Print strWhere                  'For debugging purposes only.
 
 
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
 
 
End Sub

However, the field that I have also put in, EmpName, which is a combo box, I cannot get it to filter by. Please help :)

[nico5038]

A combobox can be referred to like other fields like:
Me.comboname
There's however a catch as the combo can hold multiple columns and only the "bound" coulmn (see the Data property tab) will be returned. When you need e.g. the second column use:
Me.comboname.column(1)
The index is "zero based" and will start with 0 for the forst column, etc.

Nic;o)

[NeoPa]

I looked through your code, which illustrates some good understanding of Date filtering (a concept most people struggle with badly). One minor point, the (\) is not necessary before the (/) for the format string (It's right for the (#)s though :) - Nice).
Anyway, Please check out this tutorial (Example Filtering on a Form.) for help with your filtering. If you find you're still stuck after that then please come back and explain where your difficulty(ies) lie and we'll do what we can to help.

[Anneybo]

I had tried using the "filter a form" instructional, and I just can't seem to get it to take. I am struggling with where I should add the additional filter to the code and how exactly to add it. I've been able to do so much with access, that I know I must be stumbling on a mundane block! Please advise :)

Thanks

[NeoPa]

Can you tell me which chapter you got up to and what your confusion is exactly?
You know there is an example database attached with the finished article in don't you?

[Anneybo]

I have read through all seven chapters about 5 times now and I'm getting stuck at the filter portion. I don't have a specific filter set for this form, not sure how to do it so that it would leave it open for change every time the form pops up...
The mail problem is that I am not sure where to put the extra filter into the code and how to do so...

[NeoPa]

I'm afraid you're going to have to be a lot clearer than that. What you say makes very little sense on its own (If I had your database in front of me then it might, but I don't). You need to explain in clear simple English, what you need.
What does this even mean ? :confused:

The mail problem is that I am not sure where to put the extra filter into the code and how to do so...

[Rabbit]

I think it's just a typo. Mail = Main. But other that that, I too am confused on exactly what they want.

[Anneybo]

All I want is to add another filter into the code that I posted above. The combo box that I have in the form is "Combo6" and it reflects the text box "EmpName" on the report that is generated from the aforementioned form. When trying to use the instructions, it is not bound to a specific table. It is just a form with a report tied to it that is limited by selections made on that form. There is only one list in the combo box, not several different to choose from, they are all in text form within the combo box. Hopefully this helps a little further...

[NeoPa]

Assuming the field in the underlying record source of the report is also called [EmpName] you would need something like :

..
Private Sub cmdOK_Click()
 
Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm/dd/yyyy\#"
 
    strReport = "PTO Form"
    strField = "DateUsed"
 
    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
            strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
        End If
    Else
        If IsNull(Me.txtEndDate) Then       'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
                & " And " & Format(Me.txtEndDate, conDateFormat)
        End If
 
    End If
 
    If Not IsNull(Me!Combo6) Then
        strWhere = "((" & strWhere & ") AND ([EmpName]='" & Me!Combo6 & "'))"
    End If
 
 
 
    ' Debug.Print strWhere                  'For debugging purposes only.
 
 
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
 
 
End Sub