BETWEEN dates in cross-tab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have tried using the Parameter Value box and it will not
provide the dates between that would be needed to generate
my reports. I have tried BETWEEN [Start_Date] AND
[End_Date] under the field name, changed the total to
WHERE, still no luck. Any suggestions?
 
I have tried using the Parameter Value box and it will not
provide the dates between that would be needed to generate
my reports. I have tried BETWEEN [Start_Date] AND
[End_Date] under the field name, changed the total to
WHERE, still no luck. Any suggestions?
Click to expand...

Please open the query in SQL view and post it here. Did you specify
two parameters, [Start_Date] and [End_Date], and specify that each is
of Date/TIme type?
 
This is the SQL code info requested:

PARAMETERS [GrV Log]![Date RCVD]![Start_Date] DateTime,
[Grv Log]![Date RCVD]![End_Date] DateTime;
TRANSFORM Count([GRV LOG].[FILE#]) AS [The Value]
SELECT [GRV LOG].UNIT, Count([GRV LOG].[FILE#]) AS [Total
Of FILE#]
FROM [GRV LOG]
GROUP BY [GRV LOG].UNIT, [GRV LOG].[DATE RCVD]
PIVOT [GRV LOG].CAT;

The parameter value runs, but it does not produce the date
range(s) requested. I get all the records in the DB.
-----Original Message-----
I have tried using the Parameter Value box and it will not
provide the dates between that would be needed to generate
my reports. I have tried BETWEEN [Start_Date] AND
[End_Date] under the field name, changed the total to
WHERE, still no luck. Any suggestions?
Click to expand...

Please open the query in SQL view and post it here. Did you specify
two parameters, [Start_Date] and [End_Date], and specify that each is
of Date/TIme type?


.
Click to expand...
 
Yes, I did set an start and end in the parameter value,
both were set to date/time format.
-----Original Message-----
I have tried using the Parameter Value box and it will not
provide the dates between that would be needed to generate
my reports. I have tried BETWEEN [Start_Date] AND
[End_Date] under the field name, changed the total to
WHERE, still no luck. Any suggestions?
Click to expand...

Please open the query in SQL view and post it here. Did you specify
two parameters, [Start_Date] and [End_Date], and specify that each is
of Date/TIme type?


.
Click to expand...
 
Okay, I got it to work with the BETWEEN dates, but now I'm
having a problem with the report. I get an error of:
That the qry is not a valid field name or expression.

Darn!! So close, yet so far.
 
This is the SQL code info requested:

PARAMETERS [GrV Log]![Date RCVD]![Start_Date] DateTime,
[Grv Log]![Date RCVD]![End_Date] DateTime;
TRANSFORM Count([GRV LOG].[FILE#]) AS [The Value]
SELECT [GRV LOG].UNIT, Count([GRV LOG].[FILE#]) AS [Total
Of FILE#]
FROM [GRV LOG]
GROUP BY [GRV LOG].UNIT, [GRV LOG].[DATE RCVD]
PIVOT [GRV LOG].CAT;

The parameter value runs, but it does not produce the date
range(s) requested. I get all the records in the DB.
Click to expand...

You're defining the parameter but not using it! Your query should have
a WHERE clause with the expression

BETWEEN [GrV Log]![Date RCVD]![Start_Date] AND [Grv Log]![Date
RCVD]![End_Date]
 
Okay, I got it to work with the BETWEEN dates, but now I'm
having a problem with the report. I get an error of:
That the qry is not a valid field name or expression.

Darn!! So close, yet so far.
Click to expand...

Are you using the Query as the RecordSource for the report (correct)
or as the Control Source of some textbox or other control on the
report (incorrect, and it will give you this error)?
 
I am using it as the record source. It worked fine as a
cross-tab query without the parameter value added to it.
When I remove the parameter value, the report runs okay,
showing all records in the table. I put the parameter
value back in the query and I get the same error message
of invalid field name or expression.
 
I am using it as the record source. It worked fine as a
cross-tab query without the parameter value added to it.
When I remove the parameter value, the report runs okay,
showing all records in the table. I put the parameter
value back in the query and I get the same error message
of invalid field name or expression.
Click to expand...

Please post (again, just to check) the actual SQL of the query, and
the specific RecordSource property of the Report. I'm perplexed!
 
This is the SQL without the date parameters:
TRANSFORM Count([GRIEVANCE LOG].[FILE#]) AS [The Value]
SELECT [GRIEVANCE LOG].GRVUNIT, Count([GRIEVANCE LOG].
[FILE#]) AS [Total Of FILE#]
FROM [GRIEVANCE LOG]
GROUP BY [GRIEVANCE LOG].GRVUNIT
PIVOT [GRIEVANCE LOG].CAT;

This provides all information and the report runs as it
should.

This is the SQL after the date parameter has been added:

PARAMETERS [Start_Date] DateTime, [End_Date] DateTime;
TRANSFORM Count([GRIEVANCE LOG].[FILE#]) AS [The Value]
SELECT [GRIEVANCE LOG].GRVUNIT, Count([GRIEVANCE LOG].
[FILE#]) AS [Total Of FILE#]
FROM [GRIEVANCE LOG]
WHERE ((([GRIEVANCE LOG].[DATE RCVD]) Between [Start_Date]
And [End_Date]))
GROUP BY [GRIEVANCE LOG].GRVUNIT, [GRIEVANCE LOG].[DATE
RCVD]
PIVOT [GRIEVANCE LOG].CAT;

The query runs fine. But when I try to run the report, I
get the error: "The Microsoft Jet Database Engine does
not recognize 'Area Summary qry' as a valid field name or
expression." It's confusing to me because the query
itself runs without a problem. When I try to create
another report based on the same cross-tab query, no
fields are shown in the selection box.

Any suggestions.
 
This is the SQL after the date parameter has been added:

PARAMETERS [Start_Date] DateTime, [End_Date] DateTime;
TRANSFORM Count([GRIEVANCE LOG].[FILE#]) AS [The Value]
SELECT [GRIEVANCE LOG].GRVUNIT, Count([GRIEVANCE LOG].
[FILE#]) AS [Total Of FILE#]
FROM [GRIEVANCE LOG]
WHERE ((([GRIEVANCE LOG].[DATE RCVD]) Between [Start_Date]
And [End_Date]))
GROUP BY [GRIEVANCE LOG].GRVUNIT, [GRIEVANCE LOG].[DATE
RCVD]
PIVOT [GRIEVANCE LOG].CAT;

The query runs fine. But when I try to run the report, I
get the error: "The Microsoft Jet Database Engine does
not recognize 'Area Summary qry' as a valid field name or
expression." It's confusing to me because the query
itself runs without a problem. When I try to create
another report based on the same cross-tab query, no
fields are shown in the selection box.

Any suggestions.
Click to expand...

Look on the Report - particularly in all the Sorting and Grouping
dialogs (the Report's S&G and each section's S&G). It sounds like you
have [Area Summary qry] referenced somewhere, probably in a context
which Access would expect a fieldname. What name did you use when you
saved the above SQL? Was [Area Summmary qry] perhaps an earlier name
that you've now changed?
 
I went through every nook and cranny of my report.
Nothing refers to the query name except in the
RecordSource. Something different has come up now. The
lastest message I have received reads: "Microsoft Access
can't find the name 'End_Date'you entered in the
expression. You may have specified a control that wasn't
on the current object without specifying the correct form
or report context."

I haven't changed anything other than the parameter in the
query to make sure it still ran as it should. If I can't
get the report to run properly, I may need to set up the
query to run as a report instead until I can resolve this
issue. (Reports due now.)

Thanks John for all your help. I went to your site hoping
to find the answers needed, seems I'm not alone with
this. The only problem I have everyone keeps referring to
a form when I'm dealing with reports and queries. Am I
missing something?
-----Original Message-----
This is the SQL after the date parameter has been added:

PARAMETERS [Start_Date] DateTime, [End_Date] DateTime;
TRANSFORM Count([GRIEVANCE LOG].[FILE#]) AS [The Value]
SELECT [GRIEVANCE LOG].GRVUNIT, Count([GRIEVANCE LOG].
[FILE#]) AS [Total Of FILE#]
FROM [GRIEVANCE LOG]
WHERE ((([GRIEVANCE LOG].[DATE RCVD]) Between [Start_Date]
And [End_Date]))
GROUP BY [GRIEVANCE LOG].GRVUNIT, [GRIEVANCE LOG].[DATE
RCVD]
PIVOT [GRIEVANCE LOG].CAT;

The query runs fine. But when I try to run the report, I
get the error: "The Microsoft Jet Database Engine does
not recognize 'Area Summary qry' as a valid field name or
expression." It's confusing to me because the query
itself runs without a problem. When I try to create
another report based on the same cross-tab query, no
fields are shown in the selection box.

Any suggestions.
Click to expand...

Look on the Report - particularly in all the Sorting and Grouping
dialogs (the Report's S&G and each section's S&G). It sounds like you
have [Area Summary qry] referenced somewhere, probably in a context
which Access would expect a fieldname. What name did you use when you
saved the above SQL? Was [Area Summmary qry] perhaps an earlier name
that you've now changed?



.
Click to expand...
 
Also, no S&G on this report.
-----Original Message-----
This is the SQL after the date parameter has been added:

PARAMETERS [Start_Date] DateTime, [End_Date] DateTime;
TRANSFORM Count([GRIEVANCE LOG].[FILE#]) AS [The Value]
SELECT [GRIEVANCE LOG].GRVUNIT, Count([GRIEVANCE LOG].
[FILE#]) AS [Total Of FILE#]
FROM [GRIEVANCE LOG]
WHERE ((([GRIEVANCE LOG].[DATE RCVD]) Between [Start_Date]
And [End_Date]))
GROUP BY [GRIEVANCE LOG].GRVUNIT, [GRIEVANCE LOG].[DATE
RCVD]
PIVOT [GRIEVANCE LOG].CAT;

The query runs fine. But when I try to run the report, I
get the error: "The Microsoft Jet Database Engine does
not recognize 'Area Summary qry' as a valid field name or
expression." It's confusing to me because the query
itself runs without a problem. When I try to create
another report based on the same cross-tab query, no
fields are shown in the selection box.

Any suggestions.
Click to expand...

Look on the Report - particularly in all the Sorting and Grouping
dialogs (the Report's S&G and each section's S&G). It sounds like you
have [Area Summary qry] referenced somewhere, probably in a context
which Access would expect a fieldname. What name did you use when you
saved the above SQL? Was [Area Summmary qry] perhaps an earlier name
that you've now changed?



.
Click to expand...
 
It works in the report now. I was using a test database
to get this query to run. I went to the live DB, set up
the query, created the parameter values "[Start_Date]
date/time [End_Date] date/time." Went to the
report, reselected the query as the record source,
Shezaam!!! It worked. I think while trying to get it to
work in the test database I had made so many changes to
make it work, I may have missed something somewhere. I
guess working on a "clean slate" made it work properly.
Thanks John for all your help and patience.

P.S. Great site you have there.
 
Back
Top