Report filtered by date range and yes/no (1 Viewer)

vent · Aug 16, 2017

Hi everyone

So I have this datasheet with two expiry date fields (affiliation agreement and insurance expiry) which are totally independent of each other as well as a column with yes/no values. I was told I need to make a report criteria based on these three things. Thus the user needs to select two dates in a date range (which are based on these two columns) and either a yes and no value from a dropdown and the report would filter based on these three selections. I have made a form with the record source being the same query as the datasheet, two text boxes as a short date format for the date range and a combo box for the yes/no selection. I've looked online but haven't found anything that's this specific. If anyone can point me to some useful tutorials. That would be much appreciated!

isladogs · Aug 16, 2017

Two of the best places to look are
a) Allen Browne's website
b) Steve Bishop's Access tutorials on You Tube

vent · Aug 16, 2017

ridders said:
Two of the best places to look are
a) Allen Browne's website
b) Steve Bishop's Access tutorials on You Tube

Hi Ridders, I chose a) and followed Allen Browne's example as best I could but when I went to pressed the button that would generate the report and the date column this is based on gives me some of the dates but also ones that aren't within range, I went really specific in the date range (12/21/2019 to 2/24/2020) and should only have gotten back a couple records but I got records with 2023 and so on. Does anyone know why this is? Thank you I have attached two screenshots as well.

isladogs · Aug 16, 2017

Please post the SQL used when you click the button
It would also be useful to have a copy of the table data e.g. as an excel file

I assume these are both date fields.

pbaldy · Aug 16, 2017

Is the data type of your field date/time? It's acting like text.

vent · Aug 16, 2017

ridders said:
Please post the SQL used when you click the button
It would also be useful to have a copy of the table data e.g. as an excel file

I assume these are both date fields.

Here's the code:

Code:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "rptQuery-report"      'Put your report name in these quotes.
    strDateField = "[AA Final Date]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler

End Sub

And yes they are both date fields

vent · Aug 16, 2017

pbaldy said:
Is the data type of your field date/time? It's acting like text.

It's not date/time it is text but the format is m/d/yyyyShould I change it to Date/Time?

isladogs · Aug 16, 2017

vent said:
Hi Ridders, I chose a) and followed Allen Browne's example as best I could but when I went to pressed the button that would generate the report and the date column this is based on gives me some of the dates but also ones that aren't within range, I went really specific in the date range (12/21/2019 to 2/24/2020) and should only have gotten back a couple records but I got records with 2023 and so on. Does anyone know why this is? Thank you I have attached two screenshots as well.

So in answer to the question are these date fields, its

yes ... I mean no!

Whilst you could use CDate(mytextdatefield), it would be far better to convert to the correct datatype

BTW your code could be simplified significantly.

vent · Aug 16, 2017

ridders said:
Whilst you could use CDate(mytextdatefield), it would be far better to convert to the correct datatype

Do you mean put CDate(mytextdatefield) in format for AA Final Date's data type?

isladogs · Aug 16, 2017

No I meant in the SQL BUT I still recommend you convert to DateTime datatype

vent · Aug 16, 2017

ridders said:
No I meant in the SQL BUT I still recommend you convert to DateTime datatype

Sorry for being dense but im still not sure exactly where CDate(mytextdatefield) goes. I attached a screenshot of the report query in design view and the SQL

Also, Access won't let me change the data type in the original column without deleting some records :/

isladogs · Aug 16, 2017

Remove all the 'AgencyInfo.' from your report SQL
Not needed as you are only using the one table AgencyInfo

Then if you change to DateTime datatype, the report SQL should be OK
Otherwise use CDate([AA Final Date])

Also assuming you change the date field to DateTime datatype, this code should work (NOT TESTED)
As you can see its shorter and I think its easier to follow
Notice the '#' delimiters used for date fields

Code:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    
Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    
    'DO set the values in the next 3 lines.
    strReport = "rptQuery-report"      'Put your report name in these quotes.
    strDateField = "[AA Final Date]" 'Put your field name in the square brackets in these quotes.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
strWhere = strDateField & " Between #" & Format(Me.txtStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
    Else  'without testing on real data, I'm not sure if you need this bit
	MsgBox "Please enter a start date and an end date", vbExclamation, "Error"
        Exit Sub
    End If
 
	Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    DoCmd.OpenReport strReport, acViewPreview, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler

End Sub

vent · Aug 16, 2017

ridders said:
Remove all the 'AgencyInfo.' from your report SQL
Not needed as you are only using the one table AgencyInfo

I'm using one table yes, however there's a lot of columns in it, and most are not needed for this report, only 6 are needed. So when you say delete all agencyinfo, i'm left with nothing in the query

isladogs · Aug 16, 2017

I meant that instead of using TableName.FieldName for each item you only need Fieldname. So it starts with something like this

Code:

SELECT [Organization Name], [Subsidiary Name], Programs,   .....

BTW I'm sure its been mentioned before but field names should NEVER have special characters like / or ( or ). Spaces are also best avoided

vent · Aug 16, 2017

ridders said:
I meant that instead of using TableName.FieldName for each item you only need Fieldname. So it starts with something like this

Code:

SELECT [Organization Name], [Subsidiary Name], Programs, .....

BTW I'm sure its been mentioned before but field names should NEVER have special characters like / or ( or ). Spaces are also best avoided

So I was thinking...

looking at Allen Browne's methods, I was thinking the first might suffice for what I'm trying to do.

essentially making a query and having ">= [StartDate] < [EndDate] + 1" in the date field under criteria. Which prompts the user to enter the date in a dialog box, however would this still work if it had to be entered in a specific format? (e.g. Short Date). Thoughts?

vent · Aug 16, 2017

******UPDATE*******

Hi Everyone,

So I have a query that extracts 6 fields from a table (but some I decided to not show here). In this query, there are two end/expiry date fields (AAFinalDate and InsuranceExpiryDate) independent of each other as shown in the attachment and one yes/no field.

I need to make a report that filters the query based on these last three fields. I started with the second method giving it a second thought, Allen Browne's first method of limiting a report to a date range since it seems simpler to understand, link is right here http://allenbrowne.com/casu-08.html

This involves putting ">= [StartDate] < [EndDate] + 1" in the criteria row under date field, in this case AAFinalDate. There is a start date in the table but in terms of this report, the end/expiry date is more important to know. At first I tried it as is but that didn't work. Second time I changed it to ">= [AAFinalDate] < [AAFinalDate] + 1" but nothing shows up when I run the query and both times, i get an error saying This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables when I enter a date in this format "m/d/yyyy"

Questions:

Based on what I've described, is Allen Browne's first method compatible with what I'm trying to do? Second, How can I go further to include the second date field and the yes/no field? And also how can I make the dialog box prompt the user to enter the date in the "m/d/yyyy" format?
As always, thank you, any feedback is better than none!

gsdude · Aug 18, 2017

These easiest way i know how to filter by multiple fields is using this method here I'm using a date range for one field but you can add as many Criteria as you want to it, and Im also using combo boxes to filter data.

Code:

Dim Criteria1, Criteria2, Criteria3, SearchDateRange As String
Dim strCriteria As String


If IsNull(Me.ComboBox1) Then
    Criteria1= "[Field1] like '*'"
Else
    Criteria1= "[Field1] = " & Me.ComboBox1 & ""
End If

If IsNull(Me.ComboBox2) Then
    Criteria2= "[Field2] Like '*'"
Else
    Criteria2= "[Field2] = " & Me.ComboBox2 & ""
End If

If IsNull(Me.ComboBox3) Then
    Criteria3= "[Field3] Like '*'"
Else
    Criteria3= "[Field3] = " & Me.ComboBox3 & ""
End If

If Not IsNull(Me.txtFromDate) And Not IsNull(Me.txtToDate) Then
    SearchDateRange = "([DateStarted] >= #" & Me.txtFromDate & "# and [DateStarted] <= #" & Me.txtToDate & "#)"
End If

strCriteria = Criteria1 & " And " & Criteria2 & " And " & Criteria3 & " And " & SearchDateRange
DoCmd.OpenReport "ReportName", acViewPreview, , strCriteria

Report filtered by date range and yes/no (1 Viewer)

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Wino Moderator

Registered User.

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Registered User.

Attachments

MVP / VIP

Registered User.

Registered User.

Attachments

New member

Similar threads

Users who are viewing this thread