Crosstab query problem.

[Floyd Forbes]

I have a Crosstab query base on two queries. How do you run a Parameters in
a crosstab query?
The error shows "The micosoft jet database engine does not recongize [Please
enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[Guest]

With crosstabs, I take a two-step approach. First create a query to limit the
records such as your parameter would do. Next create the crosstab based on
the first query.

 

[Duane Hookom]

You will still get an error message if either query uses a parameter or
reference to a control on a form. The only exception to this seems to be if
you have hard-coded all column heading values into the Column Headings
property.

Select Query->Parameters and enter
[Please enter date] Date/time

Better yet, don't use parameter prompts in queries. Use references to
controls on forms. This allows you much more flexible when users enter
criteria.

--
Duane Hookom
MS Access MVP
--

With crosstabs, I take a two-step approach. First create a query to limit
the
records such as your parameter would do. Next create the crosstab based on
the first query.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a Crosstab query base on two queries. How do you run a Parameters
in
a crosstab query?
The error shows "The micosoft jet database engine does not recongize
[Please
enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[John Spencer]

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

 

[Floyd Forbes]

How do I use a Form text box reference this query.
eg forms!form1!text1

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

I have a Crosstab query base on two queries. How do you run a Parameters
in a crosstab query?
The error shows "The micosoft jet database engine does not recongize
[Please enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[John Spencer]

forms!form1!text1

The parameter is in this case a reference to a control on a form.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
[Forms]![Form1]![Text1]
Select the data type of the parameter in column 2

You might still have a problem as the query grid sometimes insists on
helping and inserts as extra set of [].

Open the query in SQL view and check the first line of the SQL statement.
It should read
Parameters [Forms]![Form1]![Text1] ...;
<<< The Crosstab SQL statement>>>

How do I use a Form text box reference this query.
eg forms!form1!text1

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

I have a Crosstab query base on two queries. How do you run a Parameters
in a crosstab query?
The error shows "The micosoft jet database engine does not recongize
[Please enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[Floyd Forbes]

Thank you. How do I use the parameter to query just the month in my query. I
have datepart("m",[date])
This expression is showing all the months. I just want to show the month I
entered.

forms!form1!text1

The parameter is in this case a reference to a control on a form.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
[Forms]![Form1]![Text1]
Select the data type of the parameter in column 2

You might still have a problem as the query grid sometimes insists on
helping and inserts as extra set of [].

Open the query in SQL view and check the first line of the SQL statement.
It should read
Parameters [Forms]![Form1]![Text1] ...;
<<< The Crosstab SQL statement>>>

How do I use a Form text box reference this query.
eg forms!form1!text1

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


I have a Crosstab query base on two queries. How do you run a Parameters
in a crosstab query?
The error shows "The micosoft jet database engine does not recongize
[Please enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[John Spencer]

Did you put the parameter in as criteria against the calculated field?

In the query grid:
Field: DatePart("m",[Date])
Criteria: [Forms]![Form1]![Text1]


You probably want to add that calculated column in a 2nd time and change
total to "WHERE" on the 2nd instance and then apply the criteria there. It
will make the query a little more efficient.

Thank you. How do I use the parameter to query just the month in my query.
I have datepart("m",[date])
This expression is showing all the months. I just want to show the month I
entered.

forms!form1!text1

The parameter is in this case a reference to a control on a form.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
[Forms]![Form1]![Text1]
Select the data type of the parameter in column 2

You might still have a problem as the query grid sometimes insists on
helping and inserts as extra set of [].

Open the query in SQL view and check the first line of the SQL statement.
It should read
Parameters [Forms]![Form1]![Text1] ...;
<<< The Crosstab SQL statement>>>

How do I use a Form text box reference this query.
eg forms!form1!text1


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


I have a Crosstab query base on two queries. How do you run a
Parameters in a crosstab query?
The error shows "The micosoft jet database engine does not recongize
[Please enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[Floyd Forbes]

I tried everything to get my parameter query to work.
It just will not query one month at a time. Do you have any others idea?

Floyd

Did you put the parameter in as criteria against the calculated field?

In the query grid:
Field: DatePart("m",[Date])
Criteria: [Forms]![Form1]![Text1]


You probably want to add that calculated column in a 2nd time and change
total to "WHERE" on the 2nd instance and then apply the criteria there.
It will make the query a little more efficient.

Thank you. How do I use the parameter to query just the month in my
query. I have datepart("m",[date])
This expression is showing all the months. I just want to show the month
I entered.

forms!form1!text1

The parameter is in this case a reference to a control on a form.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
[Forms]![Form1]![Text1]
Select the data type of the parameter in column 2

You might still have a problem as the query grid sometimes insists on
helping and inserts as extra set of [].

Open the query in SQL view and check the first line of the SQL
statement. It should read
Parameters [Forms]![Form1]![Text1] ...;
<<< The Crosstab SQL statement>>>

How do I use a Form text box reference this query.
eg forms!form1!text1


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


I have a Crosstab query base on two queries. How do you run a
Parameters in a crosstab query?
The error shows "The micosoft jet database engine does not recongize
[Please enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[Duane Hookom]

When you have an issue with a query not working as expected, it is
appropriate to post your SQL view so others can help. Provide data types of
significant fields and possibly sample records also helps.

--
Duane Hookom
MS Access MVP
--

I tried everything to get my parameter query to work.
It just will not query one month at a time. Do you have any others idea?

Floyd

Did you put the parameter in as criteria against the calculated field?

In the query grid:
Field: DatePart("m",[Date])
Criteria: [Forms]![Form1]![Text1]


You probably want to add that calculated column in a 2nd time and change
total to "WHERE" on the 2nd instance and then apply the criteria there.
It will make the query a little more efficient.

Thank you. How do I use the parameter to query just the month in my
query. I have datepart("m",[date])
This expression is showing all the months. I just want to show the month
I entered.





forms!form1!text1

The parameter is in this case a reference to a control on a form.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
[Forms]![Form1]![Text1]
Select the data type of the parameter in column 2

You might still have a problem as the query grid sometimes insists on
helping and inserts as extra set of [].

Open the query in SQL view and check the first line of the SQL
statement. It should read
Parameters [Forms]![Form1]![Text1] ...;
<<< The Crosstab SQL statement>>>

How do I use a Form text box reference this query.
eg forms!form1!text1


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


I have a Crosstab query base on two queries. How do you run a
Parameters in a crosstab query?
The error shows "The micosoft jet database engine does not recongize
[Please enter date] as a valid fileld name or expression".
Can anyone help with this problem?

Floyd

 

[Repeat3]

John, thanks for this, good to here it wasn't me being hard of understanding!

J

You might still have a problem as the query grid sometimes insists on
helping and inserts as extra set of [].