Syntax error 'missing operator' in query expression '2013 Agency_Profile.ID'.

[JD_Murray]

Where is the missing operator in the following expression:


SELECT2013Agency_Profile.ID, 2013Agency_Profile.Agency, 2013Agency_Profile.Area,2013Agency_Profile.Email, 2013Pantry_Distribution_Data.FiscalYear,2013Pantry _Tier_Data.FiscalYear AS CurrentFY_Filter,2013Pantry_Tier_Data.HoursA, 2013Pantry_Tier_Data.HoursB,2013Pantry_Tier_Data.H oursC, 2013Pantry_Tier_Data.[EmergencyFood4x/year],2013Pantry_Tier_Data.[ClientAccess/Monthly],2013Pantry_Tier_Data.[ClientAccess/Bi-weekly],2013Pantry_Tier_Data.ClientChoice,2013Pantry_Tier _Data.[Employment/JobTraining], 2013Pantry_Tier_Data.SNAPCoordinatorOnsite,2013Pan try_Tier_Data.ElectronicClientDatabase,2013Pantry_ Tier_Data.NutritionEducation, 2013Pantry_Tier_Data.MultipleServices,2013Pantry_T ier_Data.OneCourseCompleted,2013Pantry_Tier_Data.TwoCoursesCompleted, 2013Pantry_Tier_Data.FiveCoursesCompleted,2013Pant ry_Tier_Data.CBRScore20, 2013Pantry_Tier_Data.[CBRScore25+],2013Pantry_Tier_Data.CBRDate, 2013Pantry_Tier_Data.Comments,2013Pantry_Tier_Data .Tier, 2013Pantry_Tier_Data.DateTiered,2013Pantry_Tier_Da ta.TieredBy, 2013Pantry_Distribution_Data.TotalPounds,2013Pantr y_Distribution_Data.TotalClients,2013Pantry_Distri bution_Data.NutritiousPounds,2013Pantry_Distributi on_Data.[Pounds/Client],2013Pantry_Distribution_Data.[2Days/Person], 2013Pantry_Distribution_Data.[3Days/Person],2013Pantry_Distribution_Data.[4Days/Person],2013Pantry_Distribution_Data.[5Days/Person],2013Pantry_Distribution_Data.[6Days/Person],2013Pantry_Distribution_Data.[50-70%Nutritious],2013Pantry_Distribution_Data.[71-85%Nutritious], 2013Pantry_Distribution_Data.[85-100%Nutritious],2013Pantry_Distribution_Data.[MaintainTotalPounds1-10%],2013Pantry_Distribution_Data.[IncreaseNTFBPounds11-20%],2013Pantry_Distribution_Data.[IncreaseTotalPounds21%+] FROM 2013Agency_ProfileINNER JOIN 2013Pantry_Tier_Data ON 2013Agency_Profile.ID =2013Pantry_Tier_Data.ID INNER JOIN 2013Pantry_Distribution_Data ON2013Agency_Profile.ID = 2013Pantry_Distribution_Data.ID WHERE(2013Pantry_Distribution-Data.FiscalYear = 2013) AND(2013Pantry_Tier_Data.FiscalYear = 2013)

Thanks for your help; I've search but could not find the problem.

[orange]

Is there a space after SELECT? I don't see any spaces surrounding keywords INNER for example.

[John_G]

Hi -

1) There is a space missing after the SELECT keyword.
2) There are many extra spaces that should not be there, in many different words.

I can't think how those extra spaces might have got in there, but they are almost certainly causing at least part of the problem.

Fix up the spelling and the SELECT and see if that helps.

HTH

John

[JD_Murray]

I copied and pasted from a Word doc. to the web page in accessforum and somehow those spaces and missing space were posted automatically, but on the original the only spaces are after the comma in a string and where there are commands. i.e. 2013Agency_Tier_Data.Tier, 2013Pantry_Tier_Data.DateTiered,... and FROM Agency_Profile INNER JOIN 2013Pantry_Tier_Data et al. Analysis results suggest that, 'use fewer controls' and use an 'Option Explicit' statement. Oh! In the original doc there is a space between SELECT and 2013Agency_Profile.ID at the beginning of the expression

[John_G]

Hi -

The error you are getting suggests that the text that you have in MS Access does in fact have a space in
'2013 Agency_Profile.ID' (following 2013) - that is what the error is reporting.

Maybe if you show us the actual text from MS Access we can see the problem.

HTH

John

[JD_Murray]

Hi John_G,
Here is the actual text copied directly from the record source field in the form property box.
SELECT 2013Agency_Profile.ID, 2013Agency_Profile.Agency, 2013Agency_Profile.Area, 2013Agency_Profile.Email, 2013Pantry_Distribution_Data.FiscalYear, 2013Pantry_Tier_Data.FiscalYear AS CurrentFY_Filter, 2013Pantry_Tier_Data.HoursA, 2013Pantry_Tier_Data.HoursB, 2013Pantry_Tier_Data.HoursC, 2013Pantry_Tier_Data.[EmergencyFood4x/year], 2013Pantry_Tier_Data.[ClientAccess/Monthly], 2013Pantry_Tier_Data.[ClientAccess/Bi-weekly], 2013Pantry_Tier_Data.ClientChoice, 2013Pantry_Tier_Data.[Employment/JobTraining], 2013Pantry_Tier_Data.SNAPCoordinatorOnsite, 2013Pantry_Tier_Data.ElectronicClientDatabase, 2013Pantry_Tier_Data.NutritionEducation, 2013Pantry_Tier_Data.MultipleServices, 2013Pantry_Tier_Data.OneCourseCompleted, 2013Pantry_Tier_Data.TwoCoursesCompleted, 2013Pantry_Tier_Data.FiveCoursesCompleted, 2013Pantry_Tier_Data.CBRScore20, 2013Pantry_Tier_Data.[CBRScore25+], 2013Pantry_Tier_Data.CBRDate, 2013Pantry_Tier_Data.Comments, 2013Pantry_Tier_Data.Tier, 2013Pantry_Tier_Data.DateTiered, 2013Pantry_Tier_Data.TieredBy, 2013Pantry_Distribution_Data.TotalPounds, 2013Pantry_Distribution_Data.TotalClients, 2013Pantry_Distribution_Data.NutritiousPounds, 2013Pantry_Distribution_Data.[Pounds/Client], 2013Pantry_Distribution_Data.[2Days/Person], 2013Pantry_Distribution_Data.[3Days/Person], 2013Pantry_Distribution_Data.[4Days/Person], 2013Pantry_Distribution_Data.[5Days/Person], 2013Pantry_Distribution_Data.[6Days/Person], 2013Pantry_Distribution_Data.[50-70%Nutritious], 2013Pantry_Distribution_Data.[71-85%Nutritious], 2013Pantry_Distribution_Data.[85-100%Nutritious], 2013Pantry_Distribution_Data.[MaintainTotalPounds1-10%], 2013Pantry_Distribution_Data.[IncreaseNTFBPounds11-20%], 2013Pantry_Distribution_Data.[IncreaseTotalPounds21%+] FROM 2013Agency_Profile INNER JOIN 2013Pantry_Tier_Data ON 2013Agency_Profile.ID = 2013Pantry_Tier_Data.ID INNER JOIN 2013Pantry_Distribution_Data ON 2013Agency_Profile.ID = 2013Pantry_Distribution_Data.ID WHERE (2013Pantry_Distribution_Data.FiscalYear = 2013) AND (2013Pantry_Tier_Data.FiscalYear = 2013)
Thanks for all your help

[John_G]

Hi -

On close inspection, I don't see anything wrong with it. Are you sure it is that SQL that is causing the error - when does the error message occur?

What you could do is paste that text into the SQL view of a new separate query to see if it works. If it does work, then the error may be elsewhere. If you have any code that uses SQL in your form, it might be there, particularly if the code is in the On Open or On Load event - some event code that runs automatically when the form is opened. Open the form's code module and search the whole thing for "2013 " (2013-blank) - it just might find it.

HTH

John