Data comparison report with multiple date range

[Aloupha]

Hello,

I need to create a report that compares (more like displays) data from from two different date range where the user is prompted to enter the date range.

Basically, I could have the data for YTD (i.e. 1/1/14 - 4/16/14). I want to find a way to automatically generate the data for the prior year of that same date range.

I can accomplish this by running the report twice with the different date ranges. However, I would like to know if there is a way to do it in one report.



Thanks

[ItsMe]

Are you using a form to open your report?

[Aloupha]

Are you using a form to open your report?

Well, yes.

The database has a module that makes it possible to list all reports through a drop down box on a form.

My issue with this is I don't see how I could subtract from the user's [from date] input and [to date] dates.

[ItsMe]

I would use a form to collect user input and then use the variables provided to construct Where criteria.

Four unbound controls format as short date would be an idea for user input.

Where criteria would be something like this untested air code.

If IsNull(Me.txtDateStart1) Or _
   IsNull(Me.txtDateEnd1) Or _
   IsNull(Me.txtDateStart2) Or _
   IsNull(Me.txtDateEnd2) Then
MsgBox "Please enter Criteria"
Exit Sub
End If
Dim strWhere As String
Dim dtStart1 As Date
Dim dtEnd1 As Date
Dim dtStart2 As Date
Dim dtEnd2 As Date
dtStart1 = Me.txtDateStart1
dtEnd1 = Me.txtDateEnd1
dtStart2 = Me.txtDateStart2
dtEnd2 = Me.txtDateEnd2
strWhere = "[MyDateField] BETWEEN #" & dtStart1 & "# AND #" & dtEnd1 & "# Or [MyDateField] BETWEEN #" & dtStart2 & "# AND #" & dtEnd2 & "#"

DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

[Aloupha]

I tried it... however, I am getting this error "Compile error: Expected Sub, Function, or Property"

Private Sub Command5_Click() is highlighted in yellow and strWhere is selected

[ItsMe]

can you post all of the code including the sub procedure start and finish line?

[Aloupha]

can you post all of the code including the sub procedure start and finish line?

Here it is:

Private Sub Command5_Click()If IsNull(Me.txtDateStart1) Or _
   IsNull(Me.txtDateEnd1) Or _
   IsNull(Me.txtDateStart2) Or _
   IsNull(Me.txtDateEnd2) Then
   MsgBox "Please enter Criteria"
Exit Sub
End If
    Dim strWhere As String
    Dim dtStart1 As Date
    Dim dtEnd1 As Date
    Dim dtStart2 As Date
    Dim dtEnd2 As Date
    dtStart1 = Me.txtDateStart1
    dtEnd1 = Me.txtDateEnd1
    dtStart2 = Me.txtDateStart2
    dtEnd2 = Me.txtDateEnd2
    strWhere "[Date Completed] BETWEEN " & dtStart1 & " AND " & dtEnd1 & " Or [Date Completed] BETWEEN " & dtStart2 & " AND " & dtEnd2
    DoCmd.OpenReport "Test", acViewPreview, , strWhere, acWindowNormal
End Sub

[ItsMe]

You need to adjust the code to match the control names on your form. Also, you need to adjust the line of code that opens your report so Access knows the Report name to open.

If IsNull(Me.txtDateStart1) Or _
   IsNull(Me.txtDateEnd1) Or _
   IsNull(Me.txtDateStart2) Or _
   IsNull(Me.txtDateEnd2) Then
MsgBox "Please enter Criteria"
Exit Sub
End If
Dim strWhere As String
Dim dtStart1 As Date
Dim dtEnd1 As Date
Dim dtStart2 As Date
Dim dtEnd2 As Date
dtStart1 = Me.txtDateStart1
dtEnd1 = Me.txtDateEnd1
dtStart2 = Me.txtDateStart2
dtEnd2 = Me.txtDateEnd2
strWhere "[MyDateField] BETWEEN " & dtStart1 & " AND " & dtEnd1 & " Or [MyDateField] BETWEEN " & dtStart2 & " AND " & dtEnd2
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

[Aloupha]

You need to adjust the code to match the control names on your form. Also, you need to adjust the line of code that opens your report so Access knows the Report name to open.

If IsNull(Me.txtDateStart1) Or _
   IsNull(Me.txtDateEnd1) Or _
   IsNull(Me.txtDateStart2) Or _
   IsNull(Me.txtDateEnd2) Then
MsgBox "Please enter Criteria"
Exit Sub
End If
Dim strWhere As String
Dim dtStart1 As Date
Dim dtEnd1 As Date
Dim dtStart2 As Date
Dim dtEnd2 As Date
dtStart1 = Me.txtDateStart1
dtEnd1 = Me.txtDateEnd1
dtStart2 = Me.txtDateStart2
dtEnd2 = Me.txtDateEnd2
strWhere "[MyDateField] BETWEEN " & dtStart1 & " AND " & dtEnd1 & " Or [MyDateField] BETWEEN " & dtStart2 & " AND " & dtEnd2
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere

The version I posted is correct. I am using it to test how this will work. So the control names match my form, and the report name (Test) matches as well.

Same with the field name too. The field name has a space (not sure why the original author did that).

[ItsMe]

I did not understand that it was throwing an error on that line. It is missing the = operator. Another issue with it is the dates are missing the # qualifiers. I thought I would try it without first and use variables of data type Date but, it is a no go. So here, this is tested.

strWhere = "[MyDateField] BETWEEN #" & dtStart1 & "# AND #" & dtEnd1 & "# Or [MyDateField] BETWEEN #" & dtStart2 & "# AND #" & dtEnd2 & "#"

[Aloupha]

I did not understand that it was throwing an error on that line. It is missing the = operator. Another issue with it is the dates are missing the # qualifiers. I thought I would try it without first and use variables of data type Date but, it is a no go. So here, this is tested.

strWhere = "[MyDateField] BETWEEN #" & dtStart1 & "# AND #" & dtEnd1 & "# Or [MyDateField] BETWEEN #" & dtStart2 & "# AND #" & dtEnd2 & "#"

Thanks, it works!

Now, I don't know how to build a report from a form. I am looking for a tutorial.

Without the date between filter, the query I used to generate the report is as follow:

SELECT TRACKING.DET AS DET_CODE,       
       Count( TRACKING.DET ) AS TOTAL_DET,
       Sum( TRACKING.Savings ) AS TOTAL_SAV_CODE
  FROM TRACKING
 WHERE ( ( ( TRACKING.[Date of Service] ) IS NOT NULL ) 
           AND
       ( ( TRACKING.DET ) IN ( 10, 11, 12, 13, 14 )  )  ) 
 GROUP BY TRACKING.DET;

I am looking for my report to do this:

From [dtStart1] to [dtEnd1]

DET_CODE	TOTAL_DET	TOTAL_SAV_CODE
10	500	6,000.00
11	401	5,000.00
12	5	1,000.00
13	15	0.00
14	500	20,000.00

From [dtStart2] to [dtEnd2]

DET_CODE	TOTAL_DET	TOTAL_SAV_CODE
10	100	6,00.00
11	1	5,00.00
12	5	1,00.00
13	15	1,500.00
14	50	10,000.00

[ItsMe]

I don't know how to build a report from a form either. I base my reports on queries. I print my reports. I use a form to tell Access when, how, and where to print my reports.

I use Docmd.PrintReport behind a form.

If the report is based on the SQL you show in post #11, you can add additional criteria to the base SQL using the Docmd.PrintReport. The Base SQL will run first and the criteria in the Docmd will be additional criteria; applied to the data retrieved from the base SQL statement, ie Named Query Object.

Use strWhere to add additional criteria to your SQL in post #11.

[Aloupha]

I don't know how to build a report from a form either. I base my reports on queries. I print my reports. I use a form to tell Access when, how, and where to print my reports.

I use Docmd.PrintReport behind a form.

If the report is based on the SQL you show in post #11, you can add additional criteria to the base SQL using the Docmd.PrintReport. The Base SQL will run first and the criteria in the Docmd will be additional criteria; applied to the data retrieved from the base SQL statement, ie Named Query Object.

Use strWhere to add additional criteria to your SQL in post #11.

I am not sure how to use the txtDateStart1 etc... That's where I am stuck. I have very limited vb knowledge.

[ItsMe]

I am not understanding where you are stuck. The code in post #4 is to search a single date field in a query. Here is the DB I created to test the code. It uses an unbound form to collect user input. The user input is transformed into Where Criteria. The Where Criteria is applied to the Docmd.OpenReport.

You will need to place four unbound controls on a form somewhere along with a button to execute the VBA in post #4. You mentioned you did this on a test form now build some controls on your production form. Give the new controls the same names in the VBA or simply copy them from your test form onto your production form. Make a copy of your DB file so you can revert back in case of mistakes.

Take a look at the DB here and see if this helps you. The tricky part will be to replace MyDateField with the name of the field in your query. The example query provided here uses an alias to the field name in the table.

MyDateField: EquipRegDue

So if I did not have an alias in my query I would use
strWhere "[EquipRegDue] BETWEEN

Look at the example DB and see if you can incorporate the unbound controls in your DB. Use the existing tables, queries, reports and adjust the code to match your existing tables, queries, reports. The only thing new will be the unbound textbox controls and the command button.

[Aloupha]

I looked at the DB and I am still having problems.

I am not simply looking to get data between two distinct date range. I am looking to get the data and group by the date ranges and then by different factors based on the query above. In other words, for this date range 1... give me the grouped results as specified by the query. Then for date range 2, give me the grouped result again but based on date range 2.

I have no clue how to design the form to give me the desired result.