Date Range Filter by Form 2

[smalty]

I have a form with several boxes in that a user selects data to filter a report, so what ever the user has in any or all of these boxes there is code that builds this info as a filter string. The code which was obtained from this site (but I cannot find exactly where or by whom) is like this

rivate Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Debug.Print strSQL
Reports![ExpenseReport].Filter = strSQL
Reports![ExpenseReport].FilterOn = True
End If

End Sub

This works perfectly but what I have now been requested to do is include in the filter a 'from' and 'to' date, but I am totally lost as to how to change/add to this existing code.

Any help would be very much appreciated

Regards
Smalty

 

[dhookom]

The code might have been from faq181-5497.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

Hi dhookum,
THANK YOU FOR THE REPLY

The thread you mention is interesting (it wasn't the one that my example came from though) and it may well do what I need but alas I am not a programmer and not that experienced in using code and I would not know how to apply the module described.
The code I originally gave is basically all there is and it works using 4 controls in my form where the user can select 0 to 4 criteria to filter the report by. There are no 'modules' just this code and all I want to do is add two more controls, one for 'DateFrom' and the other 'DateTo' and passing this information (if selected) on to filter the report as well.
Since starting this reply I have found out where the code came from, it was from an example mdb I found on the net on another forum

http://www.access-programmers.co.uk/forums/showthread.php?t=114735

But unfortunately I am not a member of that forum and (waiting for registration confirmation) and I am under a time constraint to get this completed so tried this forum.

 

[dhookom]

I am not familiar with the code you posted and I never apply a filter to a report after it has been opened. My basic solution is something like:

Dim strWhere as String
Dim strReportName as String
strReportName = "ExpenseReport"
strWhere = "1=1 "
If not IsNull(Me.cboEmpID) Then
   'EmpID is numeric
   strWhere = strWhere & " AND EmpID = " & Me.cboEmpID
End If
If not IsNull(Me.cboDeptName) Then
   'DeptName is text
   strWhere = strWhere & " AND DeptName = """ & _
      Me.cboDeptName & """"
End If
If Not IsNull(Me.txtStartDate) Then
   'DateField is a date
   strWhere = strWhere & " And [DateField]>=#" & _
      Me.txtStartDate & "#"
End If
If Not IsNull(Me.txtEndDate) Then
   'DateField is a date
   strWhere = strWhere & " And [DateField]<=#" & _
      Me.txtEndDate & "#"
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere

I may do something more similar to the link I gave you if I have lots of reports and different criteria for each report.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

dhookom,

Good stuff, works a treat many thanx for the prompt and excellent reply.

 

[teqtaq]

dhookom,
in your example if only one parameter will be chosen and the rest were blank - would Report give ALL for this particular choosen record?


If not IsNull(Me.cboEmpID) Then
'EmpID is numeric
strWhere = strWhere & " AND EmpID = " & Me.cboEmpID
End If

What IF Me.cboEmpID IS NULL?

thanks

 

[dhookom]

If cboEmpID is null then there is no criteria applied to the EmpID field.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

dhookum,
During testing of the database I have come across another problem. It relates to the date format. You might have gathered that I work in UK and therefore have all my ettings to English 'Short Date' but as has been discussed elsewhere Access interprets all dates as US. I have seen threads on work arounds involving Function USDate or entering code somehow but again I cannot workout, due to lack of experience, how to apply this in my instance and in particular using your code above.
Are you familiar with this problem (assume you are) and if you are could you please demonstrate how to apply it to your code.

Many thanks again in anticipation

 

[dhookom]

Allen Browne is from a part of the world that also formats their dates wrong ;-) Check his tips at

http://www.allenbrowne.com/ser-36.html.

Integrating his suggestion would result in something like:

If Not IsNull(Me.txtStartDate) Then
   'DateField is a date
   strWhere = strWhere & " And [DateField]>=#" & _
     Format(Me.txtStartDate, "mm\/dd\/yyyy") & "#"
End If

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

Hi dhookum,
Once again I appreciate the swift reply and I really do understand the 'humour' in your comment 'part of the world that also formats their dates wrong .......however...I now have this code
Private Sub Command28_Click()

Dim strWhere As String
Dim strExpenseReport As String
strReportName = "ExpenseReport"
strWhere = "1=1 "
If Not IsNull(Me.cboReg) Then
'Reg is text
strWhere = strWhere & " AND Reg = """ & _
Me.cboReg & """"
End If
If Not IsNull(Me.cboItem) Then
'Item is text
strWhere = strWhere & " AND Item = """ & _
Me.cboItem & """"
End If
If Not IsNull(Me.tbDateFrom) Then
'Date is a date
strWhere = strWhere & " And [Date]>=#" & _
Format(Me.tbDateFrom, "mm\/dd\/yyyy") & "#"
End If
If Not IsNull(Me.tbDateTo) Then
'Date is a date
strWhere = strWhere & " And [Date]<=#" & _
Format(Me.tbDateTo, "mm\/dd\/yyyy") & "#"
End If
DoCmd.OpenReport strReportName, acPreview, , strWhere

End Sub
and when I enter dates in the form it still returns incorrect records. What else is required apart from the code above......what if anything do I need to do with the 'Function' described in allenbrownes site? Apologies for the 'stupid' questions but I really am a novice when it comes to code (you have probably guessed that already) but am learning at a phenominal rate thanks to you
Regards
smalty

 

[dhookom]

You didn't provide any information regarding why you think the records are "incorrect". I would add code to display the results of strWhere.

If Not IsNull(Me.tbDateTo) Then
   'Date is a date
   strWhere = strWhere & " And [Date]<=#" & _
      Format(Me.tbDateTo, "mm\/dd\/yyyy") & "#"
End If
MsgBox "strWhere: " & strWhere
DoCmd.OpenReport strReportName, acPreview, , strWhere

You can also add a text box to the report with a control source of:
=[Filter]

Allen Browne's site also has a list of bad words at

http://www.allenbrowne.com/AppIssueBadWord.html.

You will find "date" on the list.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

dhookom,
OK, I have changed the 'Date' field to be 'ExpOn' and used your MsgBox 'trick' which proved very useful. What it proved was that the Date Format code is actually working but alas it also indicated that the Where string was not being generated correctly. I will try and explain.
If I enter just a date (11/02/2008)in the DateFrom box the MsgBox shows this

strWhere: 1=1 AND THE REPORT IS GENERATED WITH ALL RECORDS

If I then go back to the form and without clearing the DateFrom box then enter a date (23/02/2008)in the DateTo box as well, the MsgBox then reads

strWhere: 1=1 AND [ExpOn]>=#02/11/2008# AND THE REPORT IS GENERATED SHOWING ONLY RECORDS AFTER 11TH FEB 2008

the if I go back to the form and manually select the date in the DateFrom box and delete it the MsgBox then reads

strWhere: 1=1 AND [ExpOn]>=#02/11/2008# AND [ExpOn]<=#02/23/2008# AND THE REPORT IS GENERATED WITH RECORDS BETWEEN THOSE DATES

So the report is generated correctly based on the string but the string is not as per the input fields.

It's like it is only accepting one date value at a time but remembering the first one if it's deleted which then enables the second on to be added to the string.

AM I GOING NUTS OR IS IT SOMETHING STUPID I AM MISSING

I hope you can follow my explaination

Regards
smalty

 

[dhookom]

I don't know what you might be doing wrong. It's time to learn to use a Break Point. You can click in the vertical bar on far left of your code adjacent to the line:

[red]O[/red] [COLOR=white red]   strWhere = "1=1 "  [/color white red]

Your code will then stop running at the Break Point and allow you to mouse over variables to see their value. You can press F8 to step through the code one line at a time or press F5 to run to the end. When you have found the issue, make sure you remove the Break Point.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

dhookom,
Once again thanks for the tip (lesson).
I have sort of discovered the problem and it doesn't exactly have anything to do with the code you provided but having said that I don't fully understand the 'why's and wherefores'
In an attempt to make the application LOOK more unique, I wanted to use bitmaps for the various BUTTONS rather than the standard Access Buttons. (I have used bitmaps elsewhere both on this database and others and have had little or no problems in using them in this way)
During my investigations to try and resolve MY problem with the report filter, I discovered that if I used a standard 'Access Command Button' instead of my bitmap to apply the 'OnClick' code to, the code works perfectly. If however I use the bitmap and apply the 'OnClick' code, the date field text boxes react in the way I have described previously but the other two combo box criteria work OK. The resolution to my problem is relatively simple.....use the standard 'Access Command Button' and I am prepared to do that but would still like to know the technical reason for it rather than just accept the fact.

 

[dhookom]

I think your issue is that a text box is not updated until it loses the focus. When you enter a date in a text box and then click a command button, the focus changes to the command button and the date you just entered is "saved" in the text box. An image control can't have the focus so the date entered into the text box isn't correct.

Try move the focus to another control prior to clicking the image to see if that makes a difference.

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

Done that.....just by clicking a button that does nothing prior to clicking the bitmap IT WORKS.
BUT!!!!!!!
How do I do that without asking the user to click another button


smalty

 

[dhookom]

Share your code behind the image click. I would expect you would need to add a line that sets the focus somewhere else like:

Me.txtSomeOtherControl.SetFocus
....

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

Code is
Private Sub Command12_Click()
Dim strWhere As String
Dim strExpenseReport As String
strReportName = "ExpenseReport"
strWhere = "1=1 "
If Not IsNull(Me.cbreg) Then
'Reg is text
strWhere = strWhere & " AND Reg = """ & _
Me.cbreg & """"
End If

If Not IsNull(Me.cbitem) Then
'Item is text
strWhere = strWhere & " AND Item = """ & _
Me.cbitem & """"
End If

If Not IsNull(Me.tbstart) Then
'Date is a date
strWhere = strWhere & " AND [ExpOn]>=#" & _
Format(Me.tbstart, "mm\/dd\/yyyy") & "#"
End If

If Not IsNull(Me.tbend) Then
'Date is a date
strWhere = strWhere & " AND [ExpOn]<=#" & _
Format(Me.tbend, "mm\/dd\/yyyy") & "#"
End If

MsgBox "strWhere: " & strWhere
DoCmd.OpenReport strReportName, acPreview, , strWhere

End Sub

 

[dhookom]

Add the line of code I suggested prior to the strWhere = "1=1 " line. You can't change the focus to a control that currently has the focus. You can find out which controls has the focus and change it like

If Me.ActiveControl.Name = "cbreg" Then
   Me.tbend.SetFocus
 Else
   Me.cbreg.SetFocus
End If
strWhere = "1=1 "

Duane
Hook'D on Access
MS Access MVP

 

[smalty]

dhookom,
Great stuff, have learnt so much. Your assistance and patience is greatly appreciated.

Many thanks

smalty