Getting syntax error while running a query in Access. The same query works in Power Query.

[sandy_xpac]

I am trying to run the below query in Access and I get the following error: Syntax error (missing operator) in query expression '(case when ModeCode in ('B','G') and claimpaiddate is not null then 'Paid' when ModeCode not in ('B','G') and ClaimStatus not like 'D%' and claimpaiddate is not null then 'Paid' else 'Denied' end),


select cd.PlanID as "Plan ID",
PlanName,
ApptDate,
PickupState,
PickupCounty,
ModeCode,
VendorName,
GroupID,
TripLegID,
Triplegstatus,
ClaimStatus,
(case when ModeCode in ('B','G') and claimpaiddate is not null then 'Paid'
when ModeCode not in ('B','G') and ClaimStatus not like 'D%' and claimpaiddate is not null then 'Paid' else 'Denied' end) as PaidorDenied,
ClaimPaidDate,
pp.ClientName,
pp.PlanState,
pp.Population,
pp.Status,
ServiceMonth,
ServiceYear,
Sum(TripLegs) as "Legs",
Sum(TripLegCost) as "Cost",
Sum(TripMiles) as "Miles"
from reports.dbo.Datamart_ClaimDetails cd JOIN
reports.dbo.pricing_PlanGroupings pp on cd.PlanID=pp.Plan_Code
where apptdate >= '01/01/2017'
Group by cd.PlanID,
PlanName,
ApptDate,
PickupState,
PickupCounty,
ModeCode,
VendorName,
GroupID,
TripLegID,
Triplegstatus,
ClaimStatus,
(case when ModeCode in ('B','G') and claimpaiddate is not null then 'Paid'
when ModeCode not in ('B','G') and ClaimStatus not like 'D%' and claimpaiddate is not null then 'Paid' else 'Denied' end),
ClaimPaidDate,
ServiceMonth,
ServiceYear,
pp.ClientName,
pp.PlanState,
pp.Population,
pp.Status

 

[Norie]

Access doesn't have Case...When, I think you might want to look at using Iif.

Perhaps something like this.

Iif(ModeCode In ('B', 'G') And Not IsNull(claimpaiddate), 'Paid', Iif(Not Claimstatus Like 'D*' and Not IsNull(claimpaiddate), 'Paid', 'Denied'))

 

[sandy_xpac]

Thanks now its giving me error "syntax error in FROM clause"

Sorry I am not familiar with access. I use crystal reports and power BI

 

[james_lankford]

where apptdate >= #01/01/2017#

 

[sandy_xpac]

I tried the above but still Syntax error FROM clause

from reports.dbo.Datamart_ClaimDetails cd JOIN
reports.dbo.pricing_PlanGroupings pp on cd.PlanID=pp.Plan_Code

 

[james_lankford]

from 
  reports.dbo.Datamart_ClaimDetails as cd 
  JOIN
  reports.dbo.pricing_PlanGroupings as pp 
    on 
    cd.PlanID = pp.Plan_Code

 

[sandy_xpac]

from 
  reports.dbo.Datamart_ClaimDetails as cd 
  JOIN
  reports.dbo.pricing_PlanGroupings as pp 
    on 
    cd.PlanID = pp.Plan_Code

Thnak you James. It still gives me "Syntax error in FROM clause"


select cd.PlanID as "Plan ID",
pp.PlanName as "Plan Name",
ApptDate,
PickupState as "Pickup State",
PickupCounty,
ModeCode as "Mode Code",
VendorName as "Vendor Name",
GroupID,
TripLegID,
Triplegstatus,
ClaimStatus,
Iif(ModeCode In ('B', 'G') And Not IsNull(claimpaiddate), 'Paid', Iif(Not Claimstatus Like 'D*' and Not IsNull(claimpaiddate), 'Paid', 'Denied')),
ClaimPaidDate,
pp.ClientName as "Client Name",
pp.PlanState,
pp.Population,
pp.Status,
pp. [Client Type],
pp. [Business Type],
ServiceMonth,
ServiceYear,
Sum(TripLegs) as "Legs",
Sum(TripLegCost) as "Cost",
Sum(TripMiles) as "Miles"
from
reports.dbo.Datamart_ClaimDetails as cd
JOIN
reports.dbo.pricing_PlanGroupings as pp
on
cd.PlanID = pp.Plan_Code
where apptdate >= #01/01/2017#
Group by cd.PlanID,
pp.PlanName,
ApptDate,
PickupState,
PickupCounty,
ModeCode,
VendorName,
GroupID,
TripLegID,
Triplegstatus,
ClaimStatus,
Iif(ModeCode In ('B', 'G') And Not IsNull(claimpaiddate), 'Paid', Iif(Not Claimstatus Like 'D*' and Not IsNull(claimpaiddate), 'Paid', 'Denied')),
ClaimPaidDate,
ServiceMonth,
ServiceYear,
pp.ClientName,
pp.PlanState,
pp.Population,
pp.[Client Type],
pp.[Business Type],
pp.Status

 

[james_lankford]

so these are MS SQL Server tables or Oracle tables ?

and you have them linked or imported in Access

if linked, how ?

and is this a regular Access query or a passthrough query