Crosstab Query and Date Parameters

  • Thread starter Thread starter kenista
  • Start date Start date
K

kenista

I have created a crosstab query that gives me all my required data in a report.

What I would like to do is to have the report based on a specific date range.

I have tried to add date parameters, but end up having to enter the dates 3
times each before the report will appear. Which I think that my users will
not like.

I have also tried the suggestion mentioned in these forums to create a
separate unbound form and pick the dates from there. But this doesn't seem to
work as the report still pulls all the data and doesn't filter by date range.

Please help
 
Thanks for your reply Allen.

Unfortunately that doesn't seem to work. My query sql is:

PARAMETERS [Forms]![FrmWhatDates]![TxtStartDate] DateTime,
[Forms]![FrmWhatDates]![TxtEndDate] DateTime;
TRANSFORM Nz(Count(Queries_Table.CompanyType),0) AS CountOfCompanyType
SELECT Queries_Table.Employee, Sum([CountOfCompanyType]) AS TotalValue
FROM Queries_Table
GROUP BY Queries_Table.Employee
PIVOT Queries_Table.CompanyType;

I enter the date onto the FrmWhatDates form and it should open the report
with the date range of the specified fields in the query (CompanyType for
each Employee). But it shows all the data, not for a specific date range.


Allen Browne said:
Firstly, in a crosstab query, the parameters must be declared.
This includes parameters of the form [Forms].[Form1].[Text0]

In query design view, click Parameters on the Query menu or ribbon, and
enter the parameters. More info:
http://allenbrowne.com/ser-67.html#Param


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


kenista said:
I have created a crosstab query that gives me all my required data in a
report.

What I would like to do is to have the report based on a specific date
range.

I have tried to add date parameters, but end up having to enter the dates
3
times each before the report will appear. Which I think that my users will
not like.

I have also tried the suggestion mentioned in these forums to create a
separate unbound form and pick the dates from there. But this doesn't seem
to
work as the report still pulls all the data and doesn't filter by date
range.

Please help
Click to expand...

.
Click to expand...
 
But your query has no WHERE clause!

In query design, add the date/time field you want to filter on to the grid.
Choose Where in the Total row.
In the Criteria row below this, enter this (as one row):
= [Forms]![FrmWhatDates]![TxtStartDate] AND
Click to expand...
< ([Forms]![FrmWhatDates]![TxtEndDate] + 1)

Other suggestions:

a) Make sure the report is not already open (even in design view.)

b) If the text boxes are unbound, set their Format property to General Date,
to ensure Access understands the value as a date.

c) Make sure you have not assigned a non-date value (e.g. "") to the text
boxes (programmatically or as a default value.)

d) Move focus out of the 2 text boxes before running the report. (The value
of the text box doesn't get updated until the focus leaves.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


kenista said:
Thanks for your reply Allen.

Unfortunately that doesn't seem to work. My query sql is:

PARAMETERS [Forms]![FrmWhatDates]![TxtStartDate] DateTime,
[Forms]![FrmWhatDates]![TxtEndDate] DateTime;
TRANSFORM Nz(Count(Queries_Table.CompanyType),0) AS CountOfCompanyType
SELECT Queries_Table.Employee, Sum([CountOfCompanyType]) AS TotalValue
FROM Queries_Table
GROUP BY Queries_Table.Employee
PIVOT Queries_Table.CompanyType;

I enter the date onto the FrmWhatDates form and it should open the report
with the date range of the specified fields in the query (CompanyType for
each Employee). But it shows all the data, not for a specific date range.


Allen Browne said:
Firstly, in a crosstab query, the parameters must be declared.
This includes parameters of the form [Forms].[Form1].[Text0]

In query design view, click Parameters on the Query menu or ribbon, and
enter the parameters. More info:
http://allenbrowne.com/ser-67.html#Param


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


kenista said:
I have created a crosstab query that gives me all my required data in a
report.

What I would like to do is to have the report based on a specific date
range.

I have tried to add date parameters, but end up having to enter the
dates
3
times each before the report will appear. Which I think that my users
will
not like.

I have also tried the suggestion mentioned in these forums to create a
separate unbound form and pick the dates from there. But this doesn't
seem
to
work as the report still pulls all the data and doesn't filter by date
range.

Please help
Click to expand...

.
Click to expand...
Click to expand...
 
Thanks Allen.

That only seems to work in the query field. When I open the report I get an
error saying "The Microsoft Office Access Database does not recognise " as a
valid field name or expression'.

I'm thinking that this comes from the query, as this error didn't appear
before?

My new sql is:

PARAMETERS [Forms]![FrmWhatDates]![TxtStartDate] DateTime,
[Forms]![FrmWhatDates]![TxtEndDate] DateTime;
TRANSFORM Nz(Count(Queries_Table.CompanyType),0) AS CountOfCompanyType
SELECT Queries_Table.Employee, Sum([CountOfCompanyType]) AS TotalValue
FROM Queries_Table
WHERE (((Queries_Table.QueryDate)>=[Forms]![FrmWhatDates]![TxtStartDate] And
(Queries_Table.QueryDate)<([Forms]![FrmWhatDates]![TxtEndDate]+1)))
GROUP BY Queries_Table.Employee
PIVOT Queries_Table.CompanyType;

I'm not sure about the QueryDate and if that needs to be in the select
statement?

Allen Browne said:
But your query has no WHERE clause!

In query design, add the date/time field you want to filter on to the grid.
Choose Where in the Total row.
In the Criteria row below this, enter this (as one row):
= [Forms]![FrmWhatDates]![TxtStartDate] AND
Click to expand...
< ([Forms]![FrmWhatDates]![TxtEndDate] + 1)

Other suggestions:

a) Make sure the report is not already open (even in design view.)

b) If the text boxes are unbound, set their Format property to General Date,
to ensure Access understands the value as a date.

c) Make sure you have not assigned a non-date value (e.g. "") to the text
boxes (programmatically or as a default value.)

d) Move focus out of the 2 text boxes before running the report. (The value
of the text box doesn't get updated until the focus leaves.
Click to expand...
 
Save the query (since you say this works), and set the RecordSource property
of the report to the name of the saved query.

If it still has problems after that, chances are that somewhere in your
report (e.g. in the Sorting And Grouping pane), you are referring to fields
that the report does not return. The query gives you just 2 fields to feed
to the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


kenista said:
Thanks Allen.

That only seems to work in the query field. When I open the report I get
an
error saying "The Microsoft Office Access Database does not recognise " as
a
valid field name or expression'.

I'm thinking that this comes from the query, as this error didn't appear
before?

My new sql is:

PARAMETERS [Forms]![FrmWhatDates]![TxtStartDate] DateTime,
[Forms]![FrmWhatDates]![TxtEndDate] DateTime;
TRANSFORM Nz(Count(Queries_Table.CompanyType),0) AS CountOfCompanyType
SELECT Queries_Table.Employee, Sum([CountOfCompanyType]) AS TotalValue
FROM Queries_Table
WHERE (((Queries_Table.QueryDate)>=[Forms]![FrmWhatDates]![TxtStartDate]
And
(Queries_Table.QueryDate)<([Forms]![FrmWhatDates]![TxtEndDate]+1)))
GROUP BY Queries_Table.Employee
PIVOT Queries_Table.CompanyType;

I'm not sure about the QueryDate and if that needs to be in the select
statement?

Allen Browne said:
But your query has no WHERE clause!

In query design, add the date/time field you want to filter on to the
grid.
Choose Where in the Total row.
In the Criteria row below this, enter this (as one row):
= [Forms]![FrmWhatDates]![TxtStartDate] AND
Click to expand...
< ([Forms]![FrmWhatDates]![TxtEndDate] + 1)

Other suggestions:

a) Make sure the report is not already open (even in design view.)

b) If the text boxes are unbound, set their Format property to General
Date,
to ensure Access understands the value as a date.

c) Make sure you have not assigned a non-date value (e.g. "") to the text
boxes (programmatically or as a default value.)

d) Move focus out of the 2 text boxes before running the report. (The
value
of the text box doesn't get updated until the focus leaves.
Click to expand...
Click to expand...
 
Sorry to be a nuisance, but I'm not sure that I follow.

My query works when I enter dates into the date range when I use the
datasheet view. But when I try to open the report based on the query via the
date form it comes up with the mentioned error.

The code for the form is:
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 = "CopyOfRptEmployeeStats2" 'Put your report name in
these quotes.
strDateField = "[QueryDate]" 'Put your field name in the square brackets
in these quotes.
lngView = acViewReport '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
 
No: you can't use the code you posted to filter the report, because the date
field is not in the report's recordsource.

Instead, put the criteria in the query the report uses.
Then open the report. It will read the query.
The query will read the values from the form.

You cannot use a WhereCondition on fields that are not in the report's
RecordSource.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


kenista said:
Sorry to be a nuisance, but I'm not sure that I follow.

My query works when I enter dates into the date range when I use the
datasheet view. But when I try to open the report based on the query via
the
date form it comes up with the mentioned error.

The code for the form is:
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 = "CopyOfRptEmployeeStats2" 'Put your report name in
these quotes.
strDateField = "[QueryDate]" 'Put your field name in the square
brackets
in these quotes.
lngView = acViewReport '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


Allen Browne said:
Save the query (since you say this works), and set the RecordSource
property
of the report to the name of the saved query.

If it still has problems after that, chances are that somewhere in your
report (e.g. in the Sorting And Grouping pane), you are referring to
fields
that the report does not return. The query gives you just 2 fields to
feed
to the report.
Click to expand...
Click to expand...
 
Thanks for all your help Allen, but now I'm back to where I started with the
error 3070.

I think that I might just report that this function isn't possible as I
can't get both the data and the date range to function properly together on
the report.
 
If you are completely stuck, the other alternative is to write code in the
Open event of the report that re-assigns the SQL of the QueryDef the report
is based on, or the RecordSource of the report itself (as a SQL string.)
 
kenista said:
Thanks Allen.

That only seems to work in the query field. When I open the report I get
an
error saying "The Microsoft Office Access Database does not recognise " as
a
valid field name or expression'.

I'm thinking that this comes from the query, as this error didn't appear
before?

My new sql is:

PARAMETERS [Forms]![FrmWhatDates]![TxtStartDate] DateTime,
[Forms]![FrmWhatDates]![TxtEndDate] DateTime;
TRANSFORM Nz(Count(Queries_Table.CompanyType),0) AS CountOfCompanyType
SELECT Queries_Table.Employee, Sum([CountOfCompanyType]) AS TotalValue
FROM Queries_Table
WHERE (((Queries_Table.QueryDate)>=[Forms]![FrmWhatDates]![TxtStartDate]
And
(Queries_Table.QueryDate)<([Forms]![FrmWhatDates]![TxtEndDate]+1)))
GROUP BY Queries_Table.Employee
PIVOT Queries_Table.CompanyType;

I'm not sure about the QueryDate and if that needs to be in the select
statement?

Allen Browne said:
But your query has no WHERE clause!

In query design, add the date/time field you want to filter on to the
grid.
Choose Where in the Total row.
In the Criteria row below this, enter this (as one row):
= [Forms]![FrmWhatDates]![TxtStartDate] AND
Click to expand...
< ([Forms]![FrmWhatDates]![TxtEndDate] + 1)

Other suggestions:

a) Make sure the report is not already open (even in design view.)

b) If the text boxes are unbound, set their Format property to General
Date,
to ensure Access understands the value as a date.

c) Make sure you have not assigned a non-date value (e.g. "") to the text
boxes (programmatically or as a default value.)

d) Move focus out of the 2 text boxes before running the report. (The
value
of the text box doesn't get updated until the focus leaves.
Click to expand...
Click to expand...
 
Back
Top