Problem with criteria in cros tab query

[Jean-Marie]

Hello all!

I’m using Access 2000 and Win XP Home, I have built a query with this SQL
statement:
SELECT tblRevenue.LicensePlate, tblRevenue. DateRev, tblRecettes.AmountRev,
"Income" AS [Transaction]
FROM tblVehicles INNER JOIN tblRevenue ON tblVehicles.LicensePlate =
tblRevenue.LicensePlate
WHERE ((tblRvenue.LicensePlate)=[Forms]![frmChooseVehicle]![cboPlateNber])
UNION SELECT tblExpenses.LicensePlate, tblExpenses.DateExp,
tblExpenses.AmountExp, "Expense" AS [Transaction]
FROM tblVehicles INNER JOIN tblExpenses ON tblVehicles.LicensePlate =
tblExpenses.LicensePlate
WHERE ((tblExpenses.LicensePlate)=[Forms]![frmChooseVehicle]![cboPlateNber]);

I have built a crosstab query based on this query that displays the fields:
Liceense Plate, Date, Income and Expense. Income and Expense being the sum of
Amounts in the Union query recorded as Income and Expense

The problem when I run the Union query it works fine, but upon running the
cross tab query I gets the message stating that "the Microsoft Jet DB engine
does not recognize ‘[Forms]![frmChooseVehicle]![cboPlateNber]’ as a valid
field name or expression". If I remove the WHERE clause the queries will run
OK. But I need to be able to choose a vehicle to analyse it data. Can someone
help me with this?
Many thanks in advance.

 

[John Vinson]

WHERE ((tblExpenses.LicensePlate)=[Forms]![frmChooseVehicle]![cboPlateNber]);

I have built a crosstab query based on this query that displays the fields:

You *may* always add a parameter to the Form's Parameters collection.
For a Crosstab query you MUST do so!

Open the query in design view; right mouseclick the grey background of
the tables, and select Parameters from the dropdown.

Copy and paste the [Forms]![frmChooseVehicle]![cboPlateNumber]
parameter to the left column of the parameters window; specify the
datatype of the field in the right column (Text, I presume). It's best
to copy rather than retype because it must match *exactly*.

John W. Vinson[MVP]

 

[Jean-Marie]

Many thanks John!

I followed these steps and now my queries work perfect. I can now move
forward.

Thank you for your help.

Jean-Marie

WHERE ((tblExpenses.LicensePlate)=[Forms]![frmChooseVehicle]![cboPlateNber]);

I have built a crosstab query based on this query that displays the fields:

You *may* always add a parameter to the Form's Parameters collection.
For a Crosstab query you MUST do so!

Open the query in design view; right mouseclick the grey background of
the tables, and select Parameters from the dropdown.

Copy and paste the [Forms]![frmChooseVehicle]![cboPlateNumber]
parameter to the left column of the parameters window; specify the
datatype of the field in the right column (Text, I presume). It's best
to copy rather than retype because it must match *exactly*.

John W. Vinson[MVP]