SQL Check Snytex Error

[ice051505]

Hi All, I am working on my query criteria on VBA now but I keep getting the snytex error from my query SQL, can anyone check it for me? Thanks!

strSQL = "SELECT weeks.Year, week_catch.Week, Species.Species, Species.SpeciesRef, week_catch.Catch, Area.Zone, Area.AreaCode, Area.TotalArea, Area.SmallArea, Area.SmallerArea, Area.SmallestArea" & _

"FROM weeks INNER JOIN (Species INNER JOIN (Gear INNER JOIN ((Fishery_Type INNER JOIN (((Group_table INNER JOIN (Area INNER JOIN aggregation_scenarios ON Area.AreaCode = aggregation_scenarios.Area)" & _

"ON Group_table.GroupRef = aggregation_scenarios.Group) INNER JOIN FNName ON Group_table.GroupName = FNName.Name) INNER JOIN week_catch ON aggregation_scenarios.ref = week_catch.Ref) ON Fishery_Type.TypeRef = aggregation_scenarios.Type)" & _

"INNER JOIN FNID ON FNName.FNID = FNID.FNID) ON Gear.GearRef = aggregation_scenarios.Gear) ON Species.SpeciesRef = week_catch.Species) ON weeks.WeekRef = week_catch.Week" & _

"GROUP BY weeks.Year, week_catch.Week, Species.Species, Species.SpeciesRef, week_catch.Catch, Area.Zone, Area.AreaCode, Area.TotalArea, Area.SmallArea, Area.SmallerArea, Area.SmallestArea" & _

"HAVING (((Species.Species, Species.SpeciesRef) IN (" & strCriteria & ")) AND ((weeks.Year) Between [forms]![Catch_Selection]![BegnYr] And [forms]![Catch_Selection]![EndYr]) AND ((Species.Species)=[forms]![Catch_Selection]![Spc_Slc]) AND ((week_catch.Catch)>0)" & _

"AND ((Area.Zone)=Switch([forms]![Catch_Selection]![Zone_slc]=1,'LFA',[forms]![Catch_Selection]![Zone_slc]=2,'BCI',[forms]![Catch_Selection]![Zone_slc]=3,'LFA') Or (Area.Zone) Like IIf([forms]![Catch_Selection]![Zone_slc]=3,'BCI')));"

 

[pr2-eugin]

Hi ice051505, Have you tried Debugging the generated SQL ? That should help you identify where the problem is..

On a rough look at your code.. you do not seem to add any space when you break the String to next line.. like..

strSQL = "SELECT weeks.Year, week_catch.Week, Species.Species,  Species.SpeciesRef, week_catch.Catch, Area.Zone, Area.AreaCode,  Area.TotalArea, Area.SmallArea, Area.SmallerArea, Area.SmallestArea[COLOR=Red][B]<insert space here>[/B][/COLOR]"  & _
 "FROM weeks INNER JOIN (Species INNER JOIN (Gear INNER JOIN  ((Fishery_Type INNER JOIN (((Group_table INNER JOIN (Area INNER JOIN  aggregation_scenarios ON Area.AreaCode = aggregation_scenarios.Area)[COLOR=Red][B]<insert space here>[/B][/COLOR]"  & _

Sort it and see if that helps..

 

[ice051505]

Thanks! I make it worked

Hi ice051505, Have you tried Debugging the generated SQL ? That should help you identify where the problem is..