Subquery problem on Search form

  • Thread starter Thread starter Carol2901
  • Start date Start date
C

Carol2901

Hello,
I have created a search form (with much help from this discussion group).
All of the critera is located in 2 linked tables. I would now like to add
criteria from a table that is linked through an intermediary table, but am
having trouble creating the subquery. 'tbl_agent_broker' links to
'tbl_trans_agents' with RecdID and 'tbl_trans_agents' links to 'tbl_property'
with TransID. Here is my code:

If Not IsNull(Me.txtAgentLast) Then
strWhere = strWhere & _
"([TransID] IN (SELECT TransID FROM tbl_trans_agents INNER JOIN
tbl_agent_broker.RecdID " & _
"WHERE tbl_agent_broker.[Last] Like """ & Me.txtAgentLast &
"*"")) AND "
End If

And here is the result:
([TransID] IN (SELECT TransID FROM tbl_trans_agents INNER JOIN
tbl_agent_broker.RecdID WHERE tbl_agent_broker.[Last] Like "Reyna*")) AND
([Archive] = False)

Thanks in advance for any help you can give me.

On a side note, if anyone knows of a relatively simple way to change field
names throughout a rather complex application, I would very much like to
clean up the "Reserved" words used when this application was created.
 
Hi Carol,
you could simply use all 4 tables in the query which is the record source
for the search form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
OK, I'll try doing it with queries.
Thanks for your help.
Carol

Jeanette Cunningham said:
Hi Carol,
you could simply use all 4 tables in the query which is the record source
for the search form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Carol2901 said:
Hello,
I have created a search form (with much help from this discussion group).
All of the critera is located in 2 linked tables. I would now like to add
criteria from a table that is linked through an intermediary table, but am
having trouble creating the subquery. 'tbl_agent_broker' links to
'tbl_trans_agents' with RecdID and 'tbl_trans_agents' links to
'tbl_property'
with TransID. Here is my code:

If Not IsNull(Me.txtAgentLast) Then
strWhere = strWhere & _
"([TransID] IN (SELECT TransID FROM tbl_trans_agents INNER JOIN
tbl_agent_broker.RecdID " & _
"WHERE tbl_agent_broker.[Last] Like """ & Me.txtAgentLast &
"*"")) AND "
End If

And here is the result:
([TransID] IN (SELECT TransID FROM tbl_trans_agents INNER JOIN
tbl_agent_broker.RecdID WHERE tbl_agent_broker.[Last] Like "Reyna*")) AND
([Archive] = False)

Thanks in advance for any help you can give me.

On a side note, if anyone knows of a relatively simple way to change field
names throughout a rather complex application, I would very much like to
clean up the "Reserved" words used when this application was created.
Click to expand...
Click to expand...
 
Back
Top