Complex Select in VB

[jpt]

I am trying to write a select statement - but I may not be able to do what I want in one step - can someone review please?
I have two tables(company, 1000) in one databse. The tables have similar fileds called companyname. I would like to select the field companyaddress from table company based upon the companyname in 1000. There are 1000 records in table 1000, and 10,000 records in table Company. However the problem is that the data in the fileds companyname are not identical. The data in 1000 is abbreviated - only the first 12 characters can be matched. ie 1000.companyname = ACME Indust.
Company.CompanyName = ACME Industries

I have tried the following:
Select Company.CompanyAddress from Company Where left(Company.CompanyName, 12) = ' left(1000.companyname), 12 '

Any suggestions? I may not have the syntax right - any corrections. Tanks.
I have tried joins and nested selects but haven't been able to make it work.
I am using visdata in vb8 to test the statements.

the from clause does not contain the table 1000

whats the database that you are using??


Originally posted by jpt:
I am trying to write a select statement - but I may not be able to do what I want in one step - can someone review please?
I have two tables(company, 1000) in one databse. The tables have similar fileds called companyname. I would like to select the field companyaddress from table company based upon the companyname in 1000. There are 1000 records in table 1000, and 10,000 records in table Company. However the problem is that the data in the fileds companyname are not identical. The data in 1000 is abbreviated - only the first 12 characters can be matched. ie 1000.companyname = ACME Indust.
Company.CompanyName = ACME Industries

I have tried the following:
Select Company.CompanyAddress from Company Where left(Company.CompanyName, 12) = ' left(1000.companyname), 12 '

Any suggestions? I may not have the syntax right - any corrections. Tanks.
I have tried joins and nested selects but haven't been able to make it work.
I am using visdata in vb8 to test the statements.

[jpt]

The database name is companyd - which is in an msaccess mdb format. Do I need the reference to table 1000 even if I am not selecting any fields to e displayed? Thanks.