anyone know how to solve this error ?


my statement looks something like this.


SELECT test1.[Opty Id], test1.[Opty Name], SJ_CIS.NEWCINSFX, test1.[Contact CIN], test1.[Contact CIN SFX], test1.[Create By (Name)], test1.[Created By (Login)], test1.[Opty Created Date], SJ_CIS.JOIN_DATE, test1.[Opty Closed Date], test1.[Closed By (Emp #)], test1.[Referral - Employee #], test1.[Referral - Employee], test1.[Sales Rep], test1.Product INTO test3
FROM test1, SJ_CIS
WHERE ((([test1].[Contact CIN] & [test1].[Contact CIN SFX])=([SJ_CIS].[NEWCINSFX])));


when i press the datasheet view, it comes out exactly what i want. but when i try to save the query an invalid argument comes out. anyone know how to solve it ?

The "best" solution actually is to use a proper join...

from Test1
Join SJ_CIS on [test1].[Contact CIN] & [test1].[Contact CIN SFX]=[SJ_CIS].[NEWCINSFX]

Or if that still gives you a headache, try splitting this into two queries.
In query1 you will do a select on Test1 to only concatinate [Contact CIN] and [Contact CIN SFX], then in the second query join this concatinated field to NEWCINSFX for your final product.

Note:
It is best if you dont use spaces or special characters, like ()#_ etc, in your database
Also you should adhere to a naming convention, tables should always start with tbl while queries start with qry, forms frm etc.
This will make it easier for you to seperate the different objects as your database grows.

Good Luck