expression question

[Slez via AccessMonster.com]

I'm trying to write a "SELECT-FROM-WHERE" expression in a field in a query to
pull in a record from a table not displayed in the query design view, but I'm
not quite sure how to structure it. I'm wondering if anyone could offer some
help.

I'd like to call the field "Estim" and SELECT Estimator FROM tblBid WHERE
ProjectID = ?.

I tried: Estimator: ("SELECT Estimator FROM Bid WHERE ProjectID=" & Project.
[ProjectID])

It displays that entire "SELECT..." statement in the field. It doesn't pull
in the value from the Estimator field.

Any suggestions?
Thanks
Slex

 

[Guest]

Try it this way:
Estimator: SELECT Estimator FROM Bid WHERE ProjectID= Project.[ProjectID]

There may be a problem using a name that exists within the query, so you may
want to change Estimator: to something else.

 

[Slez via AccessMonster.com]

Thanks for the reply! I tried the expression as you had suggested, but got
the following error:
"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."

So I added the parentheses, and then get a message:
"At most one record can be returned by the subquery."

I can click OK, but the query does not run. This is an understandable
message because there could be more than one record, but all I'm truly
looking for is the first one. Perhaps this throws a wrinkle in what I'm
trying to do.

I might need to try approaching this from a different angle, but if you have
any additional suggestions, I'll appreciate them!
Thanks again!
Slez

Try it this way:
Estimator: SELECT Estimator FROM Bid WHERE ProjectID= Project.[ProjectID]

There may be a problem using a name that exists within the query, so you may
want to change Estimator: to something else.

I'm trying to write a "SELECT-FROM-WHERE" expression in a field in a query to
pull in a record from a table not displayed in the query design view, but I'm

[quoted text clipped - 13 lines]

Thanks
Slex

 

[John Spencer]

I think that will fail with an error. Access will look at that subquery and
decide that in theory more than one record could be returned.

The following should work - it will return the first record it finds in the
Bid table that matches the criteria in the where clause. Also, note that
since you have the same field names in both bid and project tables it is
necessary to specify the table name to avoid ambiguity - computers really,
really don't handle ambiguity in names very well.

Estimator: (SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID])

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

Try it this way:
Estimator: SELECT Estimator FROM Bid WHERE ProjectID= Project.[ProjectID]

There may be a problem using a name that exists within the query, so you
may
want to change Estimator: to something else.
--
Dave Hargis, Microsoft Access MVP

I'm trying to write a "SELECT-FROM-WHERE" expression in a field in a
query to
pull in a record from a table not displayed in the query design view, but
I'm
not quite sure how to structure it. I'm wondering if anyone could offer
some
help.

I'd like to call the field "Estim" and SELECT Estimator FROM tblBid WHERE
ProjectID = ?.

I tried: Estimator: ("SELECT Estimator FROM Bid WHERE ProjectID=" &
Project.
[ProjectID])

It displays that entire "SELECT..." statement in the field. It doesn't
pull
in the value from the Estimator field.

Any suggestions?
Thanks
Slex

 

[Slez via AccessMonster.com]

I tried that expression, but I get the "invalid syntax" error. I'm not sure
why, because it looks like it should work. Please note that the only field
name that exists in both tables is ProjectID, which is where they relate.
Thanks
Slez

I think that will fail with an error. Access will look at that subquery and
decide that in theory more than one record could be returned.

The following should work - it will return the first record it finds in the
Bid table that matches the criteria in the where clause. Also, note that
since you have the same field names in both bid and project tables it is
necessary to specify the table name to avoid ambiguity - computers really,
really don't handle ambiguity in names very well.

Estimator: (SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID])

Try it this way:
Estimator: SELECT Estimator FROM Bid WHERE ProjectID= Project.[ProjectID]

[quoted text clipped - 25 lines]

 

[John Spencer]

Are you entering the expression in the query grid or are you entering it
directly into the SQL text statement

Field: Estimator: (SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID])


In an SQL Statement
(SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID]) as Estimator

The way this may fail is if Estimator is a field in your tables that are
being used in the main query. So try

(SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID]) as TheResult

If you still get syntax error, post your entire SQL statement (View: SQL
from the Menu).

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

I tried that expression, but I get the "invalid syntax" error. I'm not
sure
why, because it looks like it should work. Please note that the only
field
name that exists in both tables is ProjectID, which is where they relate.
Thanks
Slez

I think that will fail with an error. Access will look at that subquery
and
decide that in theory more than one record could be returned.

The following should work - it will return the first record it finds in
the
Bid table that matches the criteria in the where clause. Also, note that
since you have the same field names in both bid and project tables it is
necessary to specify the table name to avoid ambiguity - computers really,
really don't handle ambiguity in names very well.

Estimator: (SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID])

Try it this way:
Estimator: SELECT Estimator FROM Bid WHERE ProjectID=
Project.[ProjectID]

[quoted text clipped - 25 lines]

Thanks
Slex

 

[Slez via AccessMonster.com]

Thanks for the continued help. It appears that the field name was confusing
Access. I used your most recent expression for use in the query grid, but
called the field "Estim" instead of "Estimator" It now works as intended
with no syntax error.
Thanks again!
Slez

Are you entering the expression in the query grid or are you entering it
directly into the SQL text statement

Field: Estimator: (SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID])

In an SQL Statement
(SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID]) as Estimator

The way this may fail is if Estimator is a field in your tables that are
being used in the main query. So try

(SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID=
Project.[ProjectID]) as TheResult

If you still get syntax error, post your entire SQL statement (View: SQL
from the Menu).

I tried that expression, but I get the "invalid syntax" error. I'm not
sure

[quoted text clipped - 24 lines]