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

Text Input Filter Criteria for Report

P: 19
I have a form that generates a report based on a date range input, but I also want to add multiple "OPTIONAL" text fields to where if a user inputs a text into this field it will filter the report data by the filter and date range. And to also display an error if the text inputted does not exist in query. Here is what I have so far. I have a query and one of the fields in that query is named "Date Received" which has the following criteria.


Expand|Select|Wrap|Line Numbers
  1. >=[forms]![frmCompletedOrdersMenuDateRange]![txtDateFrom] And <=[forms]![frmCompletedOrdersMenuDateRange]![txtDateTo]
And below is my VBA Code in the form

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub cmdtoday_Click()
  4. 'Sets the Date From and Date To text boxes
  5. 'to Today's Date
  6.  
  7.     Me!txtDateFrom = Date
  8.     Me!txtDateTo = Date
  9.  
  10. End Sub
  11.  
  12. Private Sub cmdweek_Click()
  13. 'Sets the Date From and Date To text boxes
  14. 'to show complete working week (Mon - Fri)
  15.  
  16.     Dim today
  17.  
  18.     today = Weekday(Date)
  19.  
  20.     Me!txtDateFrom = DateAdd("d", today - 7, Date)
  21.     Me!txtDateTo = DateAdd("d", (today * -1) + 1, Date)
  22.  
  23. End Sub
  24.  
  25. Private Sub cmdmonth_Click()
  26. 'Sets the Date From and Date To text boxes
  27. 'to show complete month (from start to end of current month)
  28.  
  29.     Me!txtDateFrom = CDate("01/" & Month(Date) & "/" & Year(Date))
  30.     Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtDateFrom))
  31.  
  32. End Sub
  33.  
  34. Private Sub cmdyear_Click()
  35. 'Sets the Date From and Date To text boxes
  36. 'to show complete current year
  37.  
  38.     Me!txtDateFrom = CDate("01/01/" & Year(Date))
  39.     Me!txtDateTo = DateAdd("d", -1, DateAdd("yyyy", 1, Me!txtDateFrom))
  40.  
  41. End Sub
  42.  
  43. Private Sub cmdReport_Click()
  44.     On Error GoTo Err_cmdReport_Click
  45.  
  46.     Dim stDocName As String
  47.  
  48.     stDocName = "rptCompletedOrdersDateRange"
  49.  
  50.     'Check values are entered into Date From and Date To text boxes
  51.     'if so run report or cancel request
  52.  
  53.     If Len(Me.txtDateFrom & vbNullString) = 0 Or _
  54.        Len(Me.txtDateTo & vbNullString) = 0 Then
  55.         MsgBox "Please ensure that a report date range " & _
  56.                "is entered into the form", _
  57.                vbInformation, "Required Data..."
  58.         Exit Sub
  59.     Else
  60.         DoCmd.OpenReport stDocName, acViewReport
  61.     End If
  62. Exit_cmdReport_Click:
  63.     Exit Sub
  64.  
  65. Err_cmdReport_Click:
  66.     MsgBox Err.Description
  67.     Resume Exit_cmdReport_Click
  68.  
  69. End Sub
  70.  
I can't figure it out for the life of me, but I know it's probably simple. Can anyone help out?
Mar 5 '17 #1

✓ answered by astroshark

Answered my own question I inputted a criteria into the query for each seperate field.
Expand|Select|Wrap|Line Numbers
  1. Like IIf(IsNull([forms]![frmCompletedOrdersMenuDateRange]![txtRequestor]),"*",[forms]![frmCompletedOrdersMenuDateRange]![txtRequestor])
  2.  

Share this Question
Share on Google+
1 Reply


P: 19
Answered my own question I inputted a criteria into the query for each seperate field.
Expand|Select|Wrap|Line Numbers
  1. Like IIf(IsNull([forms]![frmCompletedOrdersMenuDateRange]![txtRequestor]),"*",[forms]![frmCompletedOrdersMenuDateRange]![txtRequestor])
  2.  
Mar 5 '17 #2

Post your reply

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