Can't work on formula field query.

[bubai]

I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?

[ranman256]

Brackets are fine. Somewhere, is a field not spelled correctly,
or
since you have many tables that may have the exact field name, you must differentiate them by putting the table name AND field... myTable.EmpID

[bubai]

Table references came automatically as Expression builder was used. The query involves [Item!] table and a query [PI4MatArrived]. Both of them has a common field [PI #]. So i took [Material] and [PI #] field of Item table in the query builder and under [PI #] i put a criteria "=[PI4MatArrived]![PI #]" as i want only those Materials whose corresponding PI # matches with the query PI #. But as i run, it asks for [PI4MatArrived]![PI #] value in the modal dialogue box.

[ranman256]

use a DOT like my example.

[PI4MatArrived].[PI #]

[ssanfu]

Post the SQL of your query.

Shouldn't use punctuation, special characters (exception is the underscore) or spaces in object names.
A better name for [PI #] would be [PINum] or [PI_Num].

[bubai]

Dot is not working. Still says "Enter parameter value." SQL is:

SELECT Item.Material, Item.Qty, Item.[PI #]
FROM Item
WHERE (((Item.[PI #])=[PI4MatArrived]![PI #]));

[ssanfu]

I am trying to make a query using fields from multiple tables

You don't have the table "PI4MatArrived" in the query record source.

I would expect the base query to look like: (AIR CODE!!)

SELECT Item.Material, Item.Qty, Item.[PI #]
FROM Item INNER JOIN PI4MatArrived ON Item.[PI #] = [PI4MatArrived].[PI #]

or

SELECT Item.Material, Item.Qty, Item.[PI #]
FROM Item INNER JOIN PI4MatArrived ON Item.[PI #] = [PI4MatArrived].[PI #]
WHERE [PI4MatArrived].[PI #] = [Forms]![YourFormName].[YourControlName];

How are the two tables related?
One to many relationship? Which table is the "one" table?

[June7]

What is PI4MatArrived? If it is a table - must be included in the FROM clause. And really should have a JOIN clause that links tables on PK/FK fields then the WHERE criteria probably not needed.

[bubai]

Sorry, there is a correction. "PI4MatArrived" is a query from where i get the [PI #]. Thus there is only one table and one query and they don't have any relation.The first code worked fine.
However it seems to work as well if i bring the query "PI4MatArrived" to the query builder (previously i was making the query with only Item table). In that case the SQL is:

SELECT Item.Material, Item.Qty
FROM Item, PI4MatArrived
WHERE (((Item.[PI #])=[PI4MatArrived]![PI #]));

Thanks guys! I was really stuck.

[June7]

Both datasets have [PI #] - they can be joined in query.

[bubai]

What is the advantage of joining them when they are working without any join?

[June7]

Supposed to be better performance, especially with large datasets. But if it is working satisfactorily with the WHERE clause, then good.