Syntax Error (missing Operator) in Query (1 Viewer)

[access_new_bee]

Hi all,

I get the below error when I try to run the below query in access.

Query:
SELECT
dbo_MyWorkingResults.[Permit_Number],dbo_AffiliaSyntaxtegory.affiliate_category_id as 'AffiliateCategoryID'
,[AffiliateCategory]
,dbo_CollectionCategory.coll_cat_id as 'CollectionCategoryID'
,[CollectionCategory]
,dbo_PermitType.permit_type_id as 'PermitTypeID'
,[PermitType]
,dbo_TaxaGroup.taxa_group_id as 'TaxaGroupID'
,[TaxaGroup]
,lol.BOVA as 'TargetSpeciesID'
,[TargetSpecies]
,[Permitee_FirstName] as 'PermiteeFName'
,[Permitee_LastName] as 'PermiteeLName'
,[Permitee_Affiliation] as'PermiteeAffiliation'
,[Permitee_PhoneNumber] as 'PermiteePhoneNo'
,[Permitee_Email] as 'PermiteeEmail'
,[DataEnteredBy_FirstName]
,[DataEnteredBy_LastName]
,[DataEnteredBy_Affiliation]
,[DataEnteredBy_PhoneNumber]
,[DataEnteredBy_Email]
,Switch( [CoordinateType] = 'Lat/Long (Degrees,Minutes and Seconds)' , 'DMS',
[CoordinateType] = 'Lat/Long (Degrees , Decimal Minutes)' , 'DD',
[CoordinateType] = 'Lat/Long (Decimal Degrees)' ,'DD',
[CoordinateType] = 'UTM' , 'UTM'
) as 'CoordinateTypeID'
,[CoordinateType]
,[UTM_Zone]
,[Datum]
,[Collectors]
,[Collection_Date] as 'CollectionDate'
,Null as 'SiteID'
,[Site_Description] as 'SiteDescription'
,Null as 'CollectionMethodsEffortsID'
,dbo_MyWorkingResults.CollectionMethod_Effort as 'CollectionMethodsEfforts'
,[CoordsLongorNorthing]
,[CoordsLatorEasting]
,[DDLat]
,[DDLong]
,[Waterbody]
,dbo_Counties.county_code as 'CountyID'
,[County]
,[Species]
,[BOVA_Id]
,[Macroinvertebrate_Id]
,[Quantity]
,dbo_Condition.condition_id as 'ConditionID'
,[Condition]
,dbo_Disposition.disposition_id as 'DispositionID'
,[Disposition]
,[Disposition_Location]
,[FieldTagNumber]
,[OverallComments]
,dbo_MyWorkingResults.[Permit_Status]
,dbo_MyWorkingResults.[Added_Date]
,dbo_MyWorkingResults.[Added_By]
,[MyWorkingResultsId]
,top1.earliest
FROM dbo_MyWorkingResults
inner join
(
SELECT top 1 [Permit_Number]
,[Permit_Status]
,Min([Added_Date]) as'earliest'
FROM dbo_MyWorkingResults
where dbo_MyWorkingResults.Permit_Status is null and dbo_MyWorkingResults.Permit_Number not in (99999,0,1)
and(left(TaxaGroup,50)
in (left('Other Aquatic Invertebrates (Not Water Quality Sample)',50)
,left('Macroinvertebrate Water Quality Sample (No ID for genus/species)',50)
,'Aquatic Molluscs'
,'Aquatic Crustaceans'
,'Fish'
,'Aquatic Insects'))
and dbo_MyWorkingResults.Permit_Status is null
group by [Permit_Number], [Permit_Status]
order by Min([Added_Date])) as top1
on top1.Permit_Number=dbo_MyWorkingResults.permit_Number
left join dbo_AffiliateCategory on dbo_MyWorkingResults.AffiliateCategory = LEFT(dbo_AffiliateCategory.affiliate_category_value,50)
left join dbo_CollectionCategory on dbo_MyWorkingResults.CollectionCategory = LEFT(dbo_CollectionCategory.coll_cat_desc,50)
left join dbo_PermitType on dbo_MyWorkingResults.PermitType = LEFT(dbo_PermitType.permit_type_value,50)
left join dbo_TaxaGroup on dbo_MyWorkingResults.TaxaGroup = LEFT(dbo_TaxaGroup.taxa_group_value, 50)
left join (SELECT LEFT([dbo_Species].[CommonName]+' ----- ' + [dbo_Species].[Genus] +' ' + [dbo_Species].[Species]+ ' '+[dbo_Species].[SubSpecies],50) as s, FullName, dbo_species.BOVA
from dbo_species) as lol
on lol.s = dbo_MyWorkingResults.TargetSpecies
left join dbo_Counties on dbo_Counties.county_name = dbo_MyWorkingResults.County
left join dbo_Condition on dbo_Condition.condition_value = dbo_MyWorkingResults.Condition
left join dbo_Disposition on dbo_Disposition.disposition_value=dbo_MyWorkingResults.Disposition
where left(TaxaGroup,50)
in (left('Other Aquatic Invertebrates (Not Water Quality Sample)',50)
,left('Macroinvertebrate Water Quality Sample (No ID for genus/species)',50)
,'Aquatic Molluscs'
,'Aquatic Crustaceans'
,'Fish'
,'Aquatic Insects')
and dbo_MyWorkingResults.Permit_Status is null;

Error:
Syntax Error(missing opearator) in query expression
on top1.Permit_Number=dbo_MyWorkingResults.permit_Number
left join dbo_AffiliateCategory on dbo_MyWorkingResults.AffiliateCategory = LEFT(dbo_AffiliateCategory.affiliate_category_value,50)
left join dbo_CollectionCategory on dbo_MyWorkingResults.CollectionCategory = LEFT(dbo_CollectionCategory.coll_cat_desc,50)
left join dbo_PermitType on dbo_MyWorkingResults.PermitType = LEFT(dbo_PermitType.permit_type_value,50)
left join dbo_TaxaGroup on dbo_MyWorkingResults.TaxaGroup = LEFT(dbo_TaxaGroup.taxa_group_value, 50)
left join (SELECT LEFT([dbo_Species].[CommonName]+' ----- ' + [dbo_Species].[Genus] +' ' + [dbo_Species].[Species]+ ' '+[dbo_Species].[SubSpecies],50) as s, FullName, dbo_species.BOVA
from dbo_species) as lol
on lol.s = dbo_MyWorkingResults.TargetSpecies
left join dbo_Counties on dbo_Counties.county_name = dbo_MyWorkingResults.County
left join dbo_Condition on dbo_Condition.condition_value = dbo_MyWorkingResults.Condition
left join dbo_Disposition on dbo_Disposition.disposition_value=dbo_MyWorkingResults.Disposition

Can someone please take a look ? Appreciate your help in advance !

 

[plog]

I took a look and now my eyeballs hurt.

Seriously that's a horrible mess. I see a lot of flags that you don't have a properly structured table system either (Switch function and using the Left() function on fields then doing logic on that data).

My advice is to move each subquery to its own query object and get it to work by itself. Once it does, move up a level and build the next query in the hierarchy using the sub-query that you know works. Divide and conquer, start ripping stuff out until you get rid of the syntax error, then add stuff back until you find the offender.

 

[Minty]

Holy Massive Query Batman!!!
I take it these are linked tables in a SQL Database looking at the table names ?
I would be tempted to create this in the SQL server as a view and link to the view. I'm not sure access would run that complex a query on tables of any size.

 

[access_new_bee]

Yeah I have tried the subquery thing, but still get the same error.
I think creating a view in sql server and then linking it to access would be my 'goto' solution now, as the query runs perfectly fine in sql server.

thanks for the quick response !!

 

[Minty]

If you simply copied and pasted it from SQL it won't work. There are sufficient syntax differences to make it error out.

 

[access_new_bee]

I did make syntax changes when I copied to access.

 

[Minty]

Did you paste this into the SQL view in the query designer? I have found that sometimes really long query strings get truncated if the query isn't created in the query designer.

Have you tried creating the query in VBA and running it from there?

 

[access_new_bee]

I did try creating the query in the 'ql view' in the 'query designer'. The error I posted was the one I was getting when I ran it in sql view.

 

[kevlray]

One thing, I am not sure that you can do an order by in a sub-query.

 

[access_new_bee]

Agree ! I actually created a view out of the 'sub query' and used it in my query which made it all work.

Thanks a lot for the quick responses everyone ! Appreciate it !