Parameters and crosstab queries

[scubadiver]

I have a crosstab query and, in the row heading, I have a "date" column. The
parameter I want to use is:

=[forms]![reports]![datefrom] And <=[forms]![reports]![dateto]

What is the best way to do this?

Cheers!

 

[Jerry Whittle]

You want to run a query off of a report? Never tried that myself. I know that
with forms the form must be open, but can be invisible, for it to work.

In general you want to create a select query using the parameters and get it
working right. After saving it, create your crosstab query based on the saved
select query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a crosstab query and, in the row heading, I have a "date" column. The
parameter I want to use is:

=[forms]![reports]![datefrom] And <=[forms]![reports]![dateto]

What is the best way to do this?

Cheers!

 

[scubadiver]

I've tried changing the form to [CST reports] and I am still getting the
same problem

In the criteria of the query:

Between [forms]![reports]![datefrom] And [forms]![reports]![dateto]

However I tried to create a form named "reports" and received the following
errors:

The module name'reports' is invalid.
The module name may start with the prefix Form_ or Report_.
The module can't be named Forms, Reports......

I was able to save the form as a slightly different name then rename it as
"reports". I'm thinking that you may have some problems with the code behind
the form someday.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"reports" is the name of the form, apologies for that but I already have a
crosstab query based on a select query.

If I put the parameter into the select query, when I open the crosstab query
it says

"The microsoft jet engine does not recognise ..." blah blah.

I then tried putting the parameter into a "where" statement with the
criteria into the crosstab query and I get the same message.

I then tried putting the full parameter into the "query parameter" box as
well and changed the data type to "date/time" and I get the following:

"Invalid bracketing of name '>=[forms]![reports]![datefrom] And
<=[forms]![reports]![dateto]'

Any suggestions?

You want to run a query off of a report? Never tried that myself. I know that
with forms the form must be open, but can be invisible, for it to work.

In general you want to create a select query using the parameters and get it
working right. After saving it, create your crosstab query based on the saved
select query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a crosstab query and, in the row heading, I have a "date" column. The
parameter I want to use is:

=[forms]![reports]![datefrom] And <=[forms]![reports]![dateto]

What is the best way to do this?

Cheers!

 

[Jerry Whittle]

In the criteria of the query:

Between [forms]![reports]![datefrom] And [forms]![reports]![dateto]

However I tried to create a form named "reports" and received the following
errors:

The module name'reports' is invalid.
The module name may start with the prefix Form_ or Report_.
The module can't be named Forms, Reports......

I was able to save the form as a slightly different name then rename it as
"reports". I'm thinking that you may have some problems with the code behind
the form someday.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"reports" is the name of the form, apologies for that but I already have a
crosstab query based on a select query.

If I put the parameter into the select query, when I open the crosstab query
it says

"The microsoft jet engine does not recognise ..." blah blah.

I then tried putting the parameter into a "where" statement with the
criteria into the crosstab query and I get the same message.

I then tried putting the full parameter into the "query parameter" box as
well and changed the data type to "date/time" and I get the following:

"Invalid bracketing of name '>=[forms]![reports]![datefrom] And
<=[forms]![reports]![dateto]'

Any suggestions?

You want to run a query off of a report? Never tried that myself. I know that
with forms the form must be open, but can be invisible, for it to work.

In general you want to create a select query using the parameters and get it
working right. After saving it, create your crosstab query based on the saved
select query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a crosstab query and, in the row heading, I have a "date" column. The
parameter I want to use is:

=[forms]![reports]![datefrom] And <=[forms]![reports]![dateto]

What is the best way to do this?

Cheers!

 

[John Spencer]

Pardon me, but can I suggest you post the SQL that is not working. That
often makes it easier to see what is happening.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Watch out for Access - sometimes it adds extra brackets to the parameter
declaration. The easiest way to spot (and correct) this is to switch to
the SQL View.
What you might see is
Parameters [[forms]![reports]![datefrom]] DateTime,
[[forms]![reports]![datefrom]] DateTime;
TRANSFORM ...

Change that to
Parameters [forms]![reports]![datefrom] DateTime,
[forms]![reports]![datefrom] DateTime;
TRANSFORM ...

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

My next guess is that the form being named reports is messing up things.
Try
changing the name of the form slightly and make sure that all the code is
updated to the new name.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I've tried changing the form to [CST reports] and I am still getting the
same problem

In the criteria of the query:

Between [forms]![reports]![datefrom] And [forms]![reports]![dateto]

However I tried to create a form named "reports" and received the
following
errors:

The module name'reports' is invalid.
The module name may start with the prefix Form_ or Report_.
The module can't be named Forms, Reports......

I was able to save the form as a slightly different name then rename it
as
"reports". I'm thinking that you may have some problems with the code
behind
the form someday.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:


"reports" is the name of the form, apologies for that but I already
have a
crosstab query based on a select query.

If I put the parameter into the select query, when I open the
crosstab query
it says

"The microsoft jet engine does not recognise ..." blah blah.

I then tried putting the parameter into a "where" statement with the
criteria into the crosstab query and I get the same message.

I then tried putting the full parameter into the "query parameter"
box as
well and changed the data type to "date/time" and I get the
following:

"Invalid bracketing of name '>=[forms]![reports]![datefrom] And
<=[forms]![reports]![dateto]'

Any suggestions?

:

You want to run a query off of a report? Never tried that myself. I
know that
with forms the form must be open, but can be invisible, for it to
work.

In general you want to create a select query using the parameters
and get it
working right. After saving it, create your crosstab query based on
the saved
select query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a crosstab query and, in the row heading, I have a "date"
column. The
parameter I want to use is:

=[forms]![reports]![datefrom] And <=[forms]![reports]![dateto]

What is the best way to do this?

Cheers!