Filter by Form - Date Range using single textbox

[namu23]

Hello,



May main form "RequisitionOrders" is currently based on a Query Result "qryRequisitionOrders". Here is my SQL code:

SELECT tb_OrderRequest.ID,               tb_OrderRequest.DateRequested, 
              tb_OrderRequest.AreaOfUse, 
              tb_OrderRequest.CreatedBy, 
              tb_OrderRequest.DateCreated, 
              tb_Projects.ProjectName, 
              tb_Systems.SystemCode, 
              tb_Systems.SystemName, 
              tb_Employees.FirstName, 
              tb_Employees.LastName, 
              tb_DocumentTypes.DocType, 
              tb_Departments.DepartmentCode, 
              tb_Departments.DepartmentName


FROM tb_Systems INNER JOIN 
           (tb_Projects INNER JOIN (tb_Employees INNER JOIN 
           (tb_DocumentTypes INNER JOIN 
           (tb_Departments INNER JOIN 
            tb_OrderRequest ON tb_Departments.ID = tb_OrderRequest.Department_ID) ON 
            tb_DocumentTypes.ID = tb_OrderRequest.DocType) ON 
            tb_Employees.ID = tb_OrderRequest.RequestedBy_ID) ON 
            tb_Projects.ID = tb_OrderRequest.Project_ID) ON 
            tb_Systems.ID = tb_OrderRequest.[System _ID]


WHERE ((([tb_OrderRequest].[ID] Like "*" & [Forms]![RequisitionOrders]![txtID] & "*" Or [Forms]![RequisitionOrders]![txtID] Is Null)=True) AND 
              (([tb_OrderRequest].[CreatedBy] Like "*" & [Forms]![RequisitionOrders]![txtCreatedBy] & "*" Or [Forms]![RequisitionOrders]![txtCreatedBy] Is Null)=True) AND 
              (([tb_Departments].[DepartmentCode] Like "*" & [Forms]![RequisitionOrders]![txtDepartmentCode] & "*" Or [Forms]![RequisitionOrders]![txtDepartmentCode] Is Null)=True) AND 
              (([tb_OrderRequest].[DateRequested] >= [Forms]![RequisitionOrders]![txtDateRequested] Or [Forms]![RequisitionOrders]![txtDateRequested] Is Null)=True));

What i want to do is i want to filter the form by "date range" using a single date field (in this case the "txtDateRequested"). The user may like to enter the criteria for example:


1. 01/01/15 display result would be Date Requested on January 01, 2015 only
2. >=01/01/15 <=07/01/15 display result would be all date requested from January 1 to 7, 2015

How can i do this? Please help.


Here is my current design of the form.

[June7]

The query won't recognize the logical operators, they are just characters in a text string. Eval() function can force string to be treated like an expression. Review this thread on use of Eval() function https://www.accessforums.net/program...ate-49582.html

What you want to do will not be simple.


I never use dynamic parameterized queries. Review http://www.allenbrowne.com/ser-62.html

[NTC]

your insistence on a single text box is a self inflicted complication; you really need 4 presuming this is always an AND statement:

>= 01/01/15 AND <= 07/01/15

the 1st & 3rd should be comboboxes/dropdowns with a discrete set of choices so the db knows exactly what is going on.....

logically in the end - it is 4 variable elements - you could write code that recognizes the / and then parses it into logical segments so do-able, but that would be very challenging....are you even sure they would use 01 instead of 1 in a text string??....it makes counting characters very challenging..... =1/1/15 is the same as =01/01/2015.....

[June7]

Can't have it both ways with a dynamic parameterized query. Either criteria for a single date or criteria for a date range. Example for date range that uses two textboxes and allows either to be null:

[DateRequested] BETWEEN Nz([Forms]![RequisitionOrders]![txtDateStart], #1/1/1900#) AND Nz([Forms]![RequisitionOrders]![txtDateEnd], #12/31/2900#);

Otherwise, need to use VBA as demonstrated by Allen Browne article.

[namu23]

Hi Guys,

I appreciate your valuable comments. Although it hurts my brain solving the problem, I was not able to solve the issue but I come up with I think most convenient solution for me at the moment. I would have tried changing the txtDateRequested field into a combobox (cboCriteria) rather than the user typing their own criteria.

I would have change my SQL query into VBA applyfilter command to something like this:

Private Sub Command844_Click()

If Not IsNull(Me.txtID) Then
    DoCmd.ApplyFilter "", "[ID] like ""*""& [Forms]![RequisitionOrders]![txtID] &""*"""
    End If
If Not IsNull(Me.txtCreatedBy) Then
    DoCmd.ApplyFilter "", "[CreatedBy] Like ""*""& [Forms]![RequisitionOrders]![txtCreatedBy] &""*"""
    End If


If Not IsNull(Me.txtDepartmentCode) Then
    DoCmd.ApplyFilter "", "[DepartmentCode] Like ""*"" & [Forms]![RequisitionOrders]![txtDepartmentCode] & ""*"" "
    End If
    
If IsNull(Me.cboCriteria) Then 'do nothing


ElseIf Me.cboCriteria = "This Month" Then


    DoCmd.ApplyFilter "", "(Year([qryRequisitionOrders].[DateRequested])=Year(Date()) AND Month([qryRequisitionOrders].[DateRequested])=Month(Date()))"


ElseIf Me.cboCriteria = "Last Month" Then
    DoCmd.ApplyFilter "", "(Year([qryRequisitionOrders].[DateRequested])*12+DatePart(""m"",[qryRequisitionOrders].[DateRequested])=(Year(Date())*12+DatePart(""m"",Date())-1))", ""
    End If
    
End Sub

Can somebody please give me an idea how can i insert the BETWEEN in the cboBOX? I want to work it like this:

If the user selects the BETWEEN (in cboBOX) then a popup box will appear and then they can enter their date selection. After clicking OK, the dates will then be pass on the apply filter command. (sorry if i cannot explain it very well. i hope yo understand what i am saying )

Here is the sample screenshot:

[June7]

The RowSource for combobox can be a query or a value list. User input can be restricted to the items in list or can allow any typed value as well as list items.

The image shows 'This Month' in the combobox. How did you accomplish that?

[namu23]

Hi June,

I typed in manually the value of the row source in the property sheet with a row source type of "value list" . It is no longer part of my qryRequisitionOrders.



Here is another screenshot for the "Last Month" criteria :

[June7]

I don't understand the issue. Seems you now have a workable solution. You can have BETWEEN AND as another item in the value list. Then have code do whatever based on selected value.

However, I caution that InputBox value is not easy to validate. If user does not enter a valid date, will likely trigger a run-time error. Example of input box with code to validate user input:

While strResponse < 1 Or Val(strResponse) > intMultiples Or Not IsNumeric(strResponse)
strResponse = InputBox("How many " & strTestNum & " tests? Select quantity of 1 to " & intMultiples & ".", "Multiple Tests", 1)
Wend

[namu23]

What I would like to do is when i selected the BETWEEN on the drop down list, hopefully a pop up box will appear. The pop up box contains 2 date fields, StartDate and EndDate with 2 buttons; OK and CANCEL. Now, if the user clicks on OK, how can i pass the value of StartDate and EndDate to vba and do the filter?

I have a very limited knowledge with VBA and I do not know how to handle yet or anticipate the errors when and where they will occur. As long as i limit the users from typing, i guess that will lessen the errors i will encounter haha ... I should do more research about this. Most of the solutions I found are only the results of google searching.

Can you kindly help me on my 1st question please thank you.

[June7]

If you want to simplify, could just have the two boxes on the Orders form above the combobox and no popup form. Because really going to need them there anyway. Code behind the popup form can set value of textboxes on the Orders form then the popup form can close so user can return to Orders form but the inputs are still available. Those textboxes can be hidden if you want. Then refer to those textboxes for filter criteria.