Join Expression Not Supported

[SymbionA]

I receive a RunTime 3296 - Join Expression Not Supported error when I try to run this query through VBA. However, I cannot figure out why. Any ideas?

'RunSQL = " SELECT Sm_price_rule4![RULE ID], Sm_price_rule4!BRANCH, Sm_price_rule4!DESCRIPTION, "
'RunSQL = RunSQL & " CusGroup!GroupDescription, Sm_price_rule2!EFFECTIVE, Sm_price_rule2!EXPIRY, "
'RunSQL = RunSQL & " Sm_price_rule2!ID, IIf([FORWARD CHARGE LEVELS]>'0','Y','N') AS FWD_CHG,"
'RunSQL = RunSQL & " Sm_price_rule1!BREAK, Sm_price_rule1![%], First(Sm_price_rule2!CONDITION) "
'RunSQL = RunSQL & " AS FirstOfCONDITION, Sm_price_rule4![PRODUCT GROUP], Sm_price_rule4![CUSTOMER GROUP], "
'RunSQL = RunSQL & " Sm_price_rule4![SUB TYPE] FROM (Sm_price_rule4 INNER JOIN CusGroup ON "
'RunSQL = RunSQL & " Sm_price_rule4![CUSTOMER GROUP] = CusGroup!GroupCode) INNER JOIN "
'RunSQL = RunSQL & " Sm_price_rule2 ON Sm_price_rule4![RULE ID] = Sm_price_rule2![RULE ID]) "
'RunSQL = RunSQL & " INNER JOIN Sm_price_rule1 ON (Sm_price_rule2!ID = Sm_price_rule1!ID) "
'RunSQL = RunSQL & " AND (Sm_price_rule2![RULE ID] = Sm_price_rule1![RULE ID])"
'RunSQL = RunSQL & " GROUP BY Sm_price_rule4![RULE ID], Sm_price_rule4!BRANCH, "
'RunSQL = RunSQL & " Sm_price_rule4!DESCRIPTION, CusGroup!GroupDescription, Sm_price_rule2!EFFECTIVE, "
'RunSQL = RunSQL & " Sm_price_rule2!EXPIRY, Sm_price_rule2!ID, IIf([FORWARD CHARGE LEVELS]>'0','Y','N'), "
'RunSQL = RunSQL & " Sm_price_rule1!BREAK, Sm_price_rule1![%], Sm_price_rule4![PRODUCT GROUP], "
'RunSQL = RunSQL & " Sm_price_rule4![CUSTOMER GROUP], Sm_price_rule4![SUB TYPE] "
'RunSQL = RunSQL & " Having (((Sm_price_rule2!EXPIRY)>=Now()) AND ((Sm_price_rule4![PRODUCT GROUP]) "
'RunSQL = RunSQL & " Like [Enter Manufacturer Code]) AND ((Sm_price_rule4![SUB TYPE]) Like 'R*'));"

Thanks in advance!

 

[r937]

try this --

SELECT r4.[RULE ID]
     , r4.BRANCH
     , r4.DESCRIPTION
     , cg.GroupDescription
     , r2.EFFECTIVE
     , r2.EXPIRY
     , r2.ID
     , IIf([FORWARD CHARGE LEVELS]>'0','Y','N') AS FWD_CHG
     , r1.BREAK
     , r1.[%]
     , First(r2.CONDITION) AS FirstOfCONDITION
     , r4.[PRODUCT GROUP]
     , r4.[CUSTOMER GROUP]
     , r4.[SUB TYPE] 
  FROM ((
       Sm_price_rule4 as r4
INNER 
  JOIN CusGroup as cg
    ON cg.GroupCode = r4.[CUSTOMER GROUP]
       )
INNER 
  JOIN Sm_price_rule2 as r2
    ON r2.[RULE ID] = r4.[RULE ID]
       )
INNER 
  JOIN Sm_price_rule1 as r1
    ON r1.ID = r2.ID 
 WHERE r4.[PRODUCT GROUP] Like [Enter Manufacturer Code] 
   AND r4.[SUB TYPE] Like 'R*'
   AND r2.EXPIRY >= Now() 
   AND r2.[RULE ID] = r1.[RULE ID]
 GROUP 
    BY r4.[RULE ID]
     , r4.BRANCH
     , r4.DESCRIPTION
     , cg.GroupDescription
     , r2.EFFECTIVE
     , r2.EXPIRY
     , r2.ID
     , IIf([FORWARD CHARGE LEVELS]>'0','Y','N')
     , r1.BREAK
     , r1.[%]
     , r4.[PRODUCT GROUP]
     , r4.[CUSTOMER GROUP]
     , r4.[SUB TYPE]

r937.com | rudy.ca

 

[SymbionA]

Mucha clearer! Many thanks for your response it is appreciated, i forgot to say I am using Access 97, anyway,

My code now looks like this:

Runsql = " SELECT r4.[RULE ID]"
Runsql = Runsql & " , r4.BRANCH"
Runsql = Runsql & " , r4.DESCRIPTION"
Runsql = Runsql & " , cg.GroupDescription"
Runsql = Runsql & " , r2.EFFECTIVE"
Runsql = Runsql & " , r2.EXPIRY"
Runsql = Runsql & " , r2.ID"
Runsql = Runsql & " , IIf([FORWARD CHARGE LEVELS]>'0','Y','N') AS FWD_CHG"
Runsql = Runsql & " , r1.BREAK"
Runsql = Runsql & " , r1.[%]"
Runsql = Runsql & " , First(r2.CONDITION) AS FirstOfCONDITION"
Runsql = Runsql & " , r4.[PRODUCT GROUP]"
Runsql = Runsql & " , r4.[CUSTOMER GROUP]"
Runsql = Runsql & " , r4.[SUB TYPE]"
Runsql = Runsql & " FROM (("
Runsql = Runsql & " Sm_price_rule4 As r4"
Runsql = Runsql & " INNER"
Runsql = Runsql & " JOIN CusGroup as cg"
Runsql = Runsql & " ON cg.GroupCode = r4.[CUSTOMER GROUP]"
Runsql = Runsql & " )"
Runsql = Runsql & " INNER"
Runsql = Runsql & " JOIN Sm_price_rule2 as r2"
Runsql = Runsql & " ON r2.[RULE ID] = r4.[RULE ID]"
Runsql = Runsql & " )"
Runsql = Runsql & " INNER"
Runsql = Runsql & " JOIN Sm_price_rule1 as r1"
Runsql = Runsql & " ON r1.ID = r2.ID"
Runsql = Runsql & " WHERE r4.[PRODUCT GROUP] Like [Enter Manufacturer Code]"
Runsql = Runsql & " AND r4.[SUB TYPE] Like 'R*'"
Runsql = Runsql & " AND r2.EXPIRY >= Now()"
Runsql = Runsql & " AND r2.[RULE ID] = r1.[RULE ID]"
Runsql = Runsql & " Group"
Runsql = Runsql & " BY r4.[RULE ID]"
Runsql = Runsql & " , r4.BRANCH"
Runsql = Runsql & " , r4.DESCRIPTION"
Runsql = Runsql & " , cg.GroupDescription"
Runsql = Runsql & " , r2.EFFECTIVE"
Runsql = Runsql & " , r2.EXPIRY"
Runsql = Runsql & " , r2.ID"
Runsql = Runsql & " , IIf([FORWARD CHARGE LEVELS]>'0','Y','N')"
Runsql = Runsql & " , r1.BREAK"
Runsql = Runsql & " , r1.[%]"
Runsql = Runsql & " , r4.[PRODUCT GROUP]"
Runsql = Runsql & " , r4.[CUSTOMER GROUP]"
Runsql = Runsql & " , r4.[SUB TYPE];"

I now get a run time error 2342.

A RunSQL action requires an argument consisting of an an SQL statement