Two table query not giving expected result

  • Thread starter greeknl via AccessMonster.com
  • Start date
G

greeknl via AccessMonster.com

I have two tables, contacts and companies, which are related by the companyID.
The value in the contacts tables is a result from the input form where for
avery contact one can choose the company using a combobox.

Now I would like to populate a list box using a query which as input uses
either the name (first or last) form one table or the company name from the
second table. (lets assume that in the contacts table a lot of names are the
same and that in the company table every record is unique)
I use the following query to limit the output to the selected values:
SELECT Contacts.ContactID,Contacts.LastName,Contacts.FirstName, Companies.
CompanyName FROM Companies INNER JOIN contacts ON Companies.
CompaniesID=contacts.CompanyID" & _
"where(((XXXXXX) like '*" & inputvalue & "*'))"

inputvalue is the value for the limitation of displayed data
XXXXXXX is the field to which the limitation is to be

The query gives no results (it works great when leaving out the comapnies bit)
I think something is wrong whit the innerjoin.
Please help
me I have almost ready a complete database and I would really like this to
work
 
A

Allen Browne

That approach should work, assuming you are concatenating the field name
into the string like this:
Dim strField As String
Dim strSql As String

strField = "FirstName"

strSql = "SELECT Contacts.ContactID, Contacts.LastName, " & _
"Contacts.FirstName, Companies.CompanyName " & _
"FROM Companies INNER JOIN contacts " & _
"ON Companies.CompaniesID = contacts.CompanyID " & _
"WHERE " & strField & "Like ""*" & inputvalue & "*"";"
Me.List2.RowSource = strSql

There may have been a space missing in your string before the WHERE. You can
track down this kind of thing with:
Debug.Print strSql
and then look in the Immediate window (Ctrl+G) after it runs.
 
G

greeknl via AccessMonster.com

Thank you Allen,
indeed I had forgotten the space before 'WHERE'

Question:
Is it possible by using the SQL string given to select the contacts when the
inputvalue is a companyname and thus the strField must be the field in the
companies table?
This of course when the link between the two tables is the CompanyID?

like this:

strField = "Companies.CompanyName"

strSql = "SELECT Contacts.ContactID, Contacts.LastName, " & _
"Contacts.FirstName, Companies.CompanyName " & _
"FROM Companies INNER JOIN contacts " & _
"ON Companies.CompaniesID = contacts.CompanyID " & _
"WHERE " & strField & "Like ""*" & inputvalue & "*"";"
Me.List2.RowSource = strSql

How must I link it to make it work?
 
G

greeknl via AccessMonster.com

I must add that when I used the simple form with strField = "Companies.
CompanyName" being equal to some value my form did not display the filtered
records from the contacts table so I think that the solution is to the
linkage, using the companyID, but after some trial I could't make it work
 
A

Allen Browne

Sure: that should work, since the CompanyName field is in the Companies
table (which is in the query.)

If you are having trouble getting the query statement to work, create a
query graphically, and type in some value in the Criteria row under the
company name. Check the query works, and then switch it to SQL View. There's
an example of the string you need to create.

You can even use this to get that query statement into VBA if it helps:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

And then you might want to add:
Debug.Print strSql
to your code so you can check if the string is correct.
(Use Ctrl+G to open the Immediate Window after it runs.)
 
G

greeknl via AccessMonster.com

Thank you very much for you help
especially so close to easter, here in greece everything is slowing down

Allen said:
Sure: that should work, since the CompanyName field is in the Companies
table (which is in the query.)

If you are having trouble getting the query statement to work, create a
query graphically, and type in some value in the Criteria row under the
company name. Check the query works, and then switch it to SQL View. There's
an example of the string you need to create.

You can even use this to get that query statement into VBA if it helps:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

And then you might want to add:
Debug.Print strSql
to your code so you can check if the string is correct.
(Use Ctrl+G to open the Immediate Window after it runs.)
Thank you Allen,
indeed I had forgotten the space before 'WHERE'
[quoted text clipped - 18 lines]
How must I link it to make it work?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top