Parameterize this crosstab query

[Takeadoe]

The simple query below serves my purpose, but I would like to improve
upon it by having it prompt the user for the years. In otherwords,
replace 2002 and 2006 with YEAR1 and YEAR2.

Thanks for the help. I really appreciate it.

Mike


TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between 2002 And 2006))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;

 

[Guest]

Try this ---
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between [Enter year 1] And [Enter year 2))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;

 

[Takeadoe]

Try this ---
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between [Enter year 1] And [Enter year 2))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;

--
KARL DEWEY
Build a little - Test a little

The simple query below serves my purpose, but I would like to improve
upon it by having it prompt the user for the years. In otherwords,
replace 2002 and 2006 with YEAR1 and YEAR2.

Thanks for the help. I really appreciate it.

Mike

TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between 2002 And 2006))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;- Hide quoted text -

- Show quoted text -

Hey Karl - Sure do appreciate the help. I added a "]" after "Enter
Year 2". I think it was needed. The query prompts me for the years
just fine then I get the message - "The expression is typed
incorrectly or it is too complex to be evaluated."

Any ideas at all? It seems rather simple to me.

Mike

 

[Guest]

You are correct about the closing bracket.
You should not name your field [Year] as it is a reserved word.
Try backing up a little to a select query ( In editing I found a space
between year and closing bracket in the WHERE ).
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE Vehicles.YEAR Between [Enter year 1] And [Enter year 2]
GROUP BY Vehicles.COUNTY;

--
KARL DEWEY
Build a little - Test a little

Try this ---
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between [Enter year 1] And [Enter year 2))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;

--
KARL DEWEY
Build a little - Test a little

The simple query below serves my purpose, but I would like to improve
upon it by having it prompt the user for the years. In otherwords,
replace 2002 and 2006 with YEAR1 and YEAR2.

Thanks for the help. I really appreciate it.

Mike

TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between 2002 And 2006))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;- Hide quoted text -

- Show quoted text -

Hey Karl - Sure do appreciate the help. I added a "]" after "Enter
Year 2". I think it was needed. The query prompts me for the years
just fine then I get the message - "The expression is typed
incorrectly or it is too complex to be evaluated."

Any ideas at all? It seems rather simple to me.

Mike

 

[Takeadoe]

You are correct about the closing bracket.
You should not name your field [Year] as it is a reserved word.
Try backing up a little to a select query ( In editing I found a space
between year and closing bracket in the WHERE ).
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE Vehicles.YEAR Between [Enter year 1] And [Enter year 2]
GROUP BY Vehicles.COUNTY;

--
KARL DEWEY
Build a little - Test a little

Try this ---
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between [Enter year 1] And [Enter year 2))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;
--
KARL DEWEY
Build a little - Test a little
:
The simple query below serves my purpose, but I would like to improve
upon it by having it prompt the user for the years. In otherwords,
replace 2002 and 2006 with YEAR1 and YEAR2.
Thanks for the help. I really appreciate it.
Mike
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between 2002 And 2006))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;- Hide quoted text -
- Show quoted text -

Hey Karl - Sure do appreciate the help. I added a "]" after "Enter
Year 2". I think it was needed. The query prompts me for the years
just fine then I get the message - "The expression is typed
incorrectly or it is too complex to be evaluated."

Any ideas at all? It seems rather simple to me.

Mike- Hide quoted text -

- Show quoted text -

Karl - Thanks for trying, but still no luck. Same error message.

Mike

 

[John Spencer]

I think the problem might be that the original query posted used numbers for
the Year value. Karl's suggestion defined the parameters as Text.

Try the following if the year field is a number field.


Parameters [Start Year] Long, [End Year] Long;
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE [year] Between [Start Year] And [End Year]
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;

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

You are correct about the closing bracket.
You should not name your field [Year] as it is a reserved word.
Try backing up a little to a select query ( In editing I found a space
between year and closing bracket in the WHERE ).
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE Vehicles.YEAR Between [Enter year 1] And [Enter year 2]
GROUP BY Vehicles.COUNTY;

--
KARL DEWEY
Build a little - Test a little

On Jul 16, 2:56 pm, KARL DEWEY <[email protected]>
wrote:
Try this ---
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between [Enter year 1] And [Enter year 2))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;

:
The simple query below serves my purpose, but I would like to
improve
upon it by having it prompt the user for the years. In otherwords,
replace 2002 and 2006 with YEAR1 and YEAR2.

Thanks for the help. I really appreciate it.

Mike

TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between 2002 And 2006))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;- Hide quoted text -

- Show quoted text -

Hey Karl - Sure do appreciate the help. I added a "]" after "Enter
Year 2". I think it was needed. The query prompts me for the years
just fine then I get the message - "The expression is typed
incorrectly or it is too complex to be evaluated."

Any ideas at all? It seems rather simple to me.

Mike- Hide quoted text -

- Show quoted text -

Karl - Thanks for trying, but still no luck. Same error message.

Mike

 

[Takeadoe]

I think the problem might be that the original query posted used numbers for
the Year value. Karl's suggestion defined the parameters as Text.

Try the following if the year field is a number field.

Parameters [Start Year] Long, [End Year] Long;
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE [year] Between [Start Year] And [End Year]
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;

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

You are correct about the closing bracket.
You should not name your field [Year] as it is a reserved word.
Try backing up a little to a select query ( In editing I found a space
between year and closing bracket in the WHERE ).
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE Vehicles.YEAR Between [Enter year 1] And [Enter year 2]
GROUP BY Vehicles.COUNTY;
--
KARL DEWEY
Build a little - Test a little
:
On Jul 16, 2:56 pm, KARL DEWEY <[email protected]>
wrote:
Try this ---
PARAMETERS [Enter year 1] Text ( 255 ), [Enter year 2] Text ( 255 );
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between [Enter year 1] And [Enter year 2))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;
--
KARL DEWEY
Build a little - Test a little
:
The simple query below serves my purpose, but I would like to
improve
upon it by having it prompt the user for the years. In otherwords,
replace 2002 and 2006 with YEAR1 and YEAR2.
Thanks for the help. I really appreciate it.
Mike
TRANSFORM Avg(Vehicles.vehicles) AS AvgOfvehicles
SELECT Vehicles.COUNTY
FROM Vehicles
WHERE (([year] Between 2002 And 2006))
GROUP BY Vehicles.COUNTY
PIVOT Vehicles.YEAR;- Hide quoted text -
- Show quoted text -
Hey Karl - Sure do appreciate the help. I added a "]" after "Enter
Year 2". I think it was needed. The query prompts me for the years
just fine then I get the message - "The expression is typed
incorrectly or it is too complex to be evaluated."
Any ideas at all? It seems rather simple to me.
Mike- Hide quoted text -
- Show quoted text -

Karl - Thanks for trying, but still no luck. Same error message.

Mike- Hide quoted text -

- Show quoted text -

John,

You are exactly right. I changed it from text to integer and all went
well. Thanks to all for "chipping in" on that one.

Regards,

Mike