By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,824 Members | 2,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,824 IT Pros & Developers. It's quick & easy.

Report "Filtering" with User entered Data

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. ..
  2. Private Sub cmdOK_Click()
  3.  
  4. Dim strReport As String     'Name of report to open.
  5.     Dim strField As String      'Name of your date field.
  6.     Dim strWhere As String      'Where condition for OpenReport.
  7.     Const conDateFormat = "\#mm\/dd\/yyyy\#"
  8.  
  9.     strReport = "PTO Form"
  10.     strField = "DateUsed"
  11.  
  12.     If IsNull(Me.txtStartDate) Then
  13.         If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
  14.             strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
  15.         End If
  16.     Else
  17.         If IsNull(Me.txtEndDate) Then       'Start date, but no End.
  18.             strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
  19.         Else                                'Both start and end dates.
  20.             strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
  21.                 & " And " & Format(Me.txtEndDate, conDateFormat)
  22.         End If
  23.  
  24.     End If
  25.  
  26.  
  27.  
  28.     ' Debug.Print strWhere                  'For debugging purposes only.
  29.  
  30.  
  31.     DoCmd.OpenReport strReport, acViewPreview, , strWhere
  32.  
  33.  
  34. 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 :)
Feb 8 '07 #1
Share this Question
Share on Google+
9 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Feb 9 '07 #2

NeoPa
Expert Mod 15k+
P: 31,489
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.
Feb 10 '07 #3

P: 4
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
Feb 13 '07 #4

NeoPa
Expert Mod 15k+
P: 31,489
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?
Feb 13 '07 #5

P: 4
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...
Feb 13 '07 #6

NeoPa
Expert Mod 15k+
P: 31,489
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...
Feb 13 '07 #7

Rabbit
Expert Mod 10K+
P: 12,366
I think it's just a typo. Mail = Main. But other that that, I too am confused on exactly what they want.
Feb 13 '07 #8

P: 4
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...
Feb 13 '07 #9

NeoPa
Expert Mod 15k+
P: 31,489
Assuming the field in the underlying record source of the report is also called [EmpName] you would need something like :
Expand|Select|Wrap|Line Numbers
  1. ..
  2. Private Sub cmdOK_Click()
  3.  
  4. Dim strReport As String     'Name of report to open.
  5.     Dim strField As String      'Name of your date field.
  6.     Dim strWhere As String      'Where condition for OpenReport.
  7.     Const conDateFormat = "\#mm/dd/yyyy\#"
  8.  
  9.     strReport = "PTO Form"
  10.     strField = "DateUsed"
  11.  
  12.     If IsNull(Me.txtStartDate) Then
  13.         If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
  14.             strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
  15.         End If
  16.     Else
  17.         If IsNull(Me.txtEndDate) Then       'Start date, but no End.
  18.             strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
  19.         Else                                'Both start and end dates.
  20.             strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
  21.                 & " And " & Format(Me.txtEndDate, conDateFormat)
  22.         End If
  23.  
  24.     End If
  25.  
  26.     If Not IsNull(Me!Combo6) Then
  27.         strWhere = "((" & strWhere & ") AND ([EmpName]='" & Me!Combo6 & "'))"
  28.     End If
  29.  
  30.  
  31.  
  32.     ' Debug.Print strWhere                  'For debugging purposes only.
  33.  
  34.  
  35.     DoCmd.OpenReport strReport, acViewPreview, , strWhere
  36.  
  37.  
  38. End Sub
Feb 25 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.