Multi-table Query Criteria

[rickyoswald]

I have three tables; tblQryID, tblClients and tblCases. tblClients holds client info and tblCases holds case information while tblQryID holds a string from a VB app. There is a report called rptClientCases which holds information from the tables tblClients and tblCases, these are connected as one to many. I set up a query that held the criteria for tblClients, field "Name" :
(SELECT[ID]FROM[tblQryID])

So when the VB app sends a string to the field and runs the report it will only show the report with the name that matches that of the string sent by the VB app.
I have used the same query criteria in other parts of my database, they only work if there is one table involved (beside tblQryID). I am using Access 2003 on windowsXP with all the latest updates and such. I have searched on google and such but cannot seem to find anything on it.

 

[KenReay]

Would you like to elaborate on "Only work", do you get an error message, query runs but wrong results ?

To me it should work, or two otehr possibel variations (a join on Name and Id) or a criteria of IN (SELECT ID FROM..)

One point, the use of "Name" as a column name is not a good idea. Name is a reserved word in Access (eh .name property), using it as a column name will cause propbelms sooner or later

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[rickyoswald]

Basically the code does what I want it to do provided there is only the one table involved in the query. If a second or third table is introduced, I get no error, it simply stops executing the code. I am totally stumped by this.
I will tell my DB guy to check his naming criteria