Filtering a form between 2 dates

[Keefay78]

I have a form with staff, jobs performed and the date they were performed.


I can filter the form for staff member and department

I am now looking to filter between date ranges to organize the reports better.
I have 2 text boxes [txtStartDate] and [txtEndDate] both with date pickers hopefully to search from a date field on the form called OpDate

So far I have had minimal luck, I have tried different codes etc to try and tie the fields in with the Search button for the staff and area filters, did not work
Also tried seperate button but no avail, I either get zero results or no effect on filtered staff results

Any ideas would be greatly appreciated

[ranman256]

sub btnFilter_Click()
if isNull(txtStartDate) then
   me.filterOn = false
else
    me.filter = "[OpDate] between #" & txtStartDate & "# and #" & txtEndDate & "#"  
    me.filterOn = true
endif
end sub

[ssanfu]

You should have 4 unbound text boxes (or unbound combo boxes) or you will be changing the data for whatever record currently is selected.

The code ranman256 provided is for filtering a form. If you want to filter a report, the report query will need to be modified a little.

Good luck with your project...

[Keefay78]

Thanks for both your replys. I have added ranman256's code and it works like a charm, only thing is I input dates like 01/11/2020 but the database seems to prefer the US style of 11/01/2020.

If there is a way around this I would love to know it but its not an absolute deal breaker.

ssanfu, yeh I will get to the reports soon, trying not to make the mistake of trying to run before I can walk

[Keefay78]

Also, i currently have 2 search buttons, one for the staff/area search comboboxes and 1 for the date search, they both work well, just that they cancel each other out when they are pressed.

I have tried even taking the code from the date button and adding it INTO the code for the staff search button but it just doesnt work properly.

Private Sub Command25_Click()
If Not IsNull(Me.cboSurgeonSearch) Then
strWhere = strWhere & "([Surgeon] = '" & Me.cboSurgeonSearch & "') AND "
End If


If Not IsNull(Me.cboProcSearch) Then
strWhere = strWhere & "([procedure] = '" & Me.cboProcSearch & "') AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
End If


If IsNull(txtStartDate) Then
Me.FilterOn = False
Else
Me.Filter = "[OpDate] between #" & txtStartDate & "# and #" & txtEndDate & "#"
Me.FilterOn = True
End If


Me.Filter = strWhere
Me.FilterOn = True
Me.Refresh




End Sub

[pbaldy]

Regarding date format:

http://allenbrowne.com/ser-36.html#Format

The date code should add to strWhere like the others do.

[ssanfu]

Allen Browne has an article on how to build a search form (with code). Search form

[Keefay78]

I am trying to use Allen Browne's code into my button code, im getting compile errors in (comes up in yellow but I have put it in red)

I am afraid I am at a loss, I can pik little pieces out of the code and see what he is referring to but some of it is just by me



Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([OpDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "


If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([OpDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ")"
End If

[pbaldy]

I copied the line in red to VBA and it does not highlight, so the syntax is fine. Does the textbox exist with that name?

[Keefay78]

Yeh txtStartDate is there, as is txtEndDate

OpDate is the field on the table where the job date is located

[pbaldy]

Can you attach the db here? I don't get an error with that line. I assume the constant is declared publicly somewhere? Maybe try without it just to see if that's the problem.

[Keefay78]

I have attached it here, its very small as its a work in progress

i'm sure there is something obvious in there I'm missing but I dunno

I appreciate the help though

[pbaldy]

I don't get a compile error (yellow) but there are logical flaws in the code, causing errors and/or unexpected behavior. Do you know how to set a breakpoint and follow the code? It would be instructive. Jason has FAQ here if needed:

http://www.baldyweb.com/Debugging.htm

[Keefay78]

I have tried to learn as I go, I've been able to pick up most of it but this date thing has me stumped. I think now that I have made some errors in the code I don't know how to even strip it back to basic

[pbaldy]

This would be stripped back to basic I think:

  If Not IsNull(Me.cboSurgeonSearch) Then
    strWhere = strWhere & "([Surgeon] = '" & Me.cboSurgeonSearch & "') AND "
  End If

  If Not IsNull(Me.cboProcSearch) Then
    strWhere = strWhere & "([procedure] = '" & Me.cboProcSearch & "') AND "
  End If

  lngLen = Len(strWhere) - 5
  If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
  Else
    strWhere = Left$(strWhere, lngLen)
    Me.Filter = strWhere
    Me.FilterOn = True
    Me.Refresh
  End If

So the way you add the dates is after the test of the procedure combo but before setting lngLen, add two stand-alone tests similar to the two tests of the combos. Each adds to strWhere when appropriate, including the " AND " at the end, since you have code to strip that off. Oh, and put the constant at the top. Actually I'd have it in a standard module so you can use it anywhere. My constants are always declared in one place so I can easily find them.

Public Const conJetDate As String = "\#mm\/dd\/yyyy\#"

Not to throw too much at you, but you aren't declaring your variables which isn't good practice. This will force the issue:

http://www.baldyweb.com/OptionExplicit.htm