Hello All,
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.
Thanks