Problem with criteria in cros tab query

J

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.
 
J

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]
 
J

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

John Vinson said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Union query 5
BUILDING UNION QUERY 10
SUM in a UNION query 2
Another query too complex problem 3
INSERT UNION QUERY 3
Union Query with two crosstabs 2
Cross tab query 0
Union Query Edit 1

Top