I'm wondering if anyone can help me by either directing me towards a source that would help me understand Access syntax better or help me figure out if it's possible to convert this SQL syntax to Access. I've tried but can't seem to get it to work.
my SQL is
SELECT substring(c.[DealerNumber],11,3) AS InsGrp, substring(c.[DealerNumber],1,2) AS DlrSt, c.DealerNumber, c.ContractNumber, substring(c.[DealerNumber],13,1) AS Expr1, c.ownerfname, c.ownerlname, c.address1, c.address2, c.city, c.state, c.zip, c.carrierid, carrier.carriername, dbo.InsuranceGroup.TPA, dbo.CLAIMS.ClaimNumber, dbo.CLAIMSCOMP.Status, dbo.claims.date, dbo.CLAIMS.CorrectiveAction FROM contract C INNER JOIN carrier ON c.carrierid = carrier.carrierid INNER JOIN dbo.InsuranceGroup ON substring(c.[DealerNumber],11,3) = dbo.InsuranceGroup.InsuranceGroup INNER JOIN dbo.claims ON dbo.claims.contractnumber = c.ContractNumber INNER JOIN dbo.claimscomp ON claims.claimnumber = claimscomp.claimnumber WHERE (((substring(c.[DealerNumber],1,2))= 'CA') AND ((substring(c.[DealerNumber],13,1))='M')) OR (((substring(c.[DealerNumber],1,2))='IA') AND ((substring(c.[DealerNumber],13,1))<>'M')) AND (((dbo.InsuranceGroup.TPA) In ('Dimension','NASC','NASC Old'))) AND (((dbo.CLAIMSCOMP.Status) In ('07','93','94','de','du','dn'))) AND dbo.claims.[Date] > '2009-03-11 13:40:38.000'
and I seem to get errors when trying to put this into Access. I've tried the GUI but the join on substring I can't figure out. This query works fine in SQL, however, I'm trying to get it into an Access query so a user can run the report whenever they want without having to come to IT.