SubQuery using Calculated Field won't work

[dchendrickson]

I am using Access2002/XP.

I need some guidence regarding syntax for a subquery that
is refering to a calculated field for its WHERE statement.

At the end of my query I would like to look up a few
items and concatenate them into a label. The values for
this concatenated label come from a couple of differnt
tables (all shown in the query window). I tried to create
one large SQL subquery to do this, but ended up breaking
it into parts to debug.

I have a calculated field that returns an ID number from
a table - an integer. The calculated field is a simple
subquery and it works correctly.

ConnectsCID: (SELECT p.IOCardConnector FROM tblIOCardPin
AS p WHERE p.IOCardPinID = tblIOCardPin!PinConnectsTo)

The 'p' alias is used since this subquery calls back upon
its own table for the value. This returns the ID of a
particular connector.

The next calculated field is to return the Label for that
connector. That information is in a different table and
looks like:

ConnectsCLabel: (SELECT ConnectorLabel FROM
tblIOCardConnector AS c WHERE c!IOCardConnectorID =
ConnectsCID)

This is the subquery causing problems - When I run the
query it asks me to enter the parameter value for
ConnectsCID. So the value is not being pulled from the
first calculated field into this one. If I enter what I
know is the proper value, the subquery returns the
correct information (entered either by typing into the
parameter dialog box or replacing the reference to ...CID
with the same value hardcoded in). If I eliminate all the
subquery text leaving only:

ConnectsCLabel: ConnectsCID

the value of ...CID is returned in the ...Label
calculated field. So in this case the value is being
pulled in.

What in the SELECT... statement causes the call to the
first calculated field to grenade?

Thanks for your time and for sharing your knowledge.

-dc

 

[Ken Snell]

Let's try an obvious change first.

In queries, you use the . (not the !) between table and field names. Change
your SQL statements so that the ! is changed to . character. For example:

ConnectsCID: (SELECT p.IOCardConnector FROM tblIOCardPin
AS p WHERE p.IOCardPinID = tblIOCardPin.PinConnectsTo)

 

[dchendrickson]

Ken,

In my frustration of trying to get what I thought was a
straightforward problem solved, I began trying all sorts
of things. One of those was to substitute the ! for the .

I guess I never changed it back.

I never did solve the problem of why I couldn't refer to
another calculated field in a subquery WHERE statement,
but I eventually got to my solution by re-combining the
separate calculated fields into a nested subquery - which
was my initial approach. Somehow all the planets aligned
and it worked.

But back to the original question - for future reference -
should I be able to refer to a calculated field [calc1]
in another calculated field [calc2] whose formula is a
subquery (SELECT... FROM... WHERE... = [calc1])?

I know [calc1] is working (it puts out the right answer
when I run the query). And the alternate form of [calc2]
using the DLookup construct works too - although SLOWLY -
calc2: DLookup("...","...","... = " & [calc1]).

Thanks for your insight.

-dc

 

[Ken Snell]

sually, you don't refer to the name of the calculated field. There are times
when this works, and other times when it doesn't. I'm sure there is logic
for it, but I haven't worked through it yet! ;-)

So, when you want to use a calculated field in the subquery, try referring
to the actual expression that is the calculated field's expression.

If you're using a calculated field in a join, you must be sure that the
field formats match. That may mean using a wrapper function (such as CLng,
CStr, etc.) around the field name in order to convert to a different type.

If this isn't working, then post more details about the SQL statements for
all the queries and let's see what we can work out.

Calling a function from a subquery will be slow!
--
Ken Snell
<MS ACCESS MVP>

Ken,

In my frustration of trying to get what I thought was a
straightforward problem solved, I began trying all sorts
of things. One of those was to substitute the ! for the .

I guess I never changed it back.

I never did solve the problem of why I couldn't refer to
another calculated field in a subquery WHERE statement,
but I eventually got to my solution by re-combining the
separate calculated fields into a nested subquery - which
was my initial approach. Somehow all the planets aligned
and it worked.

But back to the original question - for future reference -
should I be able to refer to a calculated field [calc1]
in another calculated field [calc2] whose formula is a
subquery (SELECT... FROM... WHERE... = [calc1])?

I know [calc1] is working (it puts out the right answer
when I run the query). And the alternate form of [calc2]
using the DLookup construct works too - although SLOWLY -
calc2: DLookup("...","...","... = " & [calc1]).

Thanks for your insight.

-dc

-----Original Message-----
Let's try an obvious change first.

In queries, you use the . (not the !) between table and field names. Change
your SQL statements so that the ! is changed to . character. For example:

ConnectsCID: (SELECT p.IOCardConnector FROM tblIOCardPin
AS p WHERE p.IOCardPinID = tblIOCardPin.PinConnectsTo)

--
Ken Snell
<MS ACCESS MVP>





.