Developer Forum

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?

On Wed, 29 Oct 2003 13:08:57 -0800,



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?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/

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.


not
generate
you specify
that each is

Yes, I did set an start and end in the parameter value,
both were set to date/time format.


not
generate
you specify
that each is

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.
not
generate

On Thu, 30 Oct 2003 06:50:24 -0800, "Lena"



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]


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/

On Thu, 30 Oct 2003 07:07:17 -0800,



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)?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/

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'm
report (correct)
on the

On Fri, 31 Oct 2003 05:57:38 -0800,



Please post (again, just to check) the actual SQL of the query, and
the specific RecordSource property of the Report. I'm perplexed!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/

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.


it.
query, and
perplexed!

On Mon, 3 Nov 2003 06:40:00 -0800, "Lena"



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?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/

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?

[Start_Date]
I
or
Grouping
sounds like you
a context
use when you
earlier name

Also, no S&G on this report.

[Start_Date]
I
or
Grouping
sounds like you
a context
use when you
earlier name

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.

it.
query, and
perplexed!

On Thu, 6 Nov 2003 10:32:30 -0800,



<g> Glad it finally got straightend out!

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://www.yqcomputer.com/