Peter; Thanks for your reply. I originally developed the SQL string by copying the SQL view from the Access IDE. Ultimately, the problem was a syntactical error in how the control reference was incorporated into the SQL string. It wanted the reference concatenated into the string, which I had tried previously, but specifically it wanted the reference enclosed in single quotes which was not required in the SQL within Access. Again thanks for your reply. Kingfisher ----- Original Message ---- From: Peter T. Fairchild <[EMAIL PROTECTED]> To: [email protected] Sent: Monday, April 30, 2007 8:05:41 AM Subject: Re: [ms_access] SQL in VBA
Dear Kingfisher, The easiest way to check SQL is to step through the code, get the SQL and open a query window, change the view to SQL and drop it in there. You can change to the design view, correct it and then copy the SQL back into your code. If it won't change view, your SQL is incorrect. Create the query in the design view, get the records you want , switch to the sql view and get your corrected SQL to go back into your code. Kind regards Peter Peter T. Fairchild Consultant en Informatique peterfairchild@ bluewin.ch +41-(0)79-715- 9155 Portable +41-(0)21-800- 4044 Office "Form and Function are one" Frank Lloyd Wright 1867 - 1959 kingfisher2xx4 wrote: > I am trying to write a query in VB for an Access Module and keep > getting an error on the join. Specifically "Join expression not > supported." > > Here is the text I an assigning to the ADO command : > > > "INSERT INTO CTimeReportTbl ( Timecard!EmpID, TimeCardHours! ateWorked, > TimeCardHours! Minutes, TimeCardHours! RateIDLookUpTime CardHours! > CompCode ) SELECT TimeCard!EmpID, TimeCardHours. DateWorked, > TimeCardHours! Minutes,TimeCard Hours!CompCodeLo okUp FROM (TimeCard > INNER JOIN TimeCardHours ON TimeCard!TimeCardID = TimeCardHours! > TimeCardID) WHERE (((TimeCard! EmpID) = [Forms]![TC_ Select_Form] ![EmpID > Combo]) And ((TimeCardHours! CompCodeLookUp) = 'CTA' Or (TimeCardHours! > CompCodeLookUp) = 'CTU')) ORDER BY TimeCard!EmpID, TimeCardHours! > DateWorked;" > > Your help is greatly appreciated! > > > > > Yahoo! Groups Links > > > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com [Non-text portions of this message have been removed]
