Click on query result header and get an error message

I have a form which opens in datasheet view.  The record source is a query with two tables joined.  The SQL for the query is:

SELECT Activities.Day, Projects.[Project name], Projects.Client, Activities.Hours, Activities.Activity, Activities.Employee
FROM Projects INNER JOIN Activities ON Projects.[Project ID] = Activities.[Project ID]
WHERE (((Activities.Day) Is Not Null))
ORDER BY Activities.Day, Projects.[Project name];

Open in new window


When I just open the query and click on the little down arrow next to the field header named Project name I get an error:

"Syntax error (missing operator) in query expression 'Project name'.

????

--Steve
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
Check that you haven't got a double space anywhere between "project" and "name" in the project name field, or that the field name in the table doesn't have a double space between the words.
0
SteveL13Author Commented:
Nope,.  No double spaces anywhere.  What is strange is that if I make the query be a make table query and then open the table it created it works fine.
0
SimonCommented:
OK, in the form properties, check that there isn't a filter based on project name and that filteronload isn't set to true.
Also check that there isn't an event associated with on_click for the form that references project name.

If you change the form to use the table you just created instead of teh query, does the problem occur then? If so, it's definitely a problem with the form properties.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

SteveL13Author Commented:
No filer in the form properties.  Filteronload = No.  

and

"If you change the form to use the table you just created instead of the query, does the problem occur then?" No it does not.
0
SimonCommented:
Hmm. are the objects that you join in your datasource query both tables?
"FROM Projects INNER JOIN Activities"
... or is one or both of them another query?

Does the form have a module (i.e. any event handler code) or embedded macros? If so, I'd examine them for references to [project name].

Other than that. I'd resort to suggesting the you revert to using your query as the datasource for the form, but do a 'compact and repair' on the database.
0
SteveL13Author Commented:
Both are tables.  Nothing in the form that would cause this that I can find.  No event handlers, or embedded macros.  

I think I'll just use the table that the maketable query creates and then dump the data from it when the form closes.

Is a real mystery.
0
PatHartmanCommented:
Have you defined a caption in the table?   The caption is what is displayed in the header of forms in DS view.  But that might be conflicting with a label that is attached to the control.  I'm not sure which takes precedence these days.  I avoided captions for years due to all sorts of subtle and not so subtle problems and this may be a new one.

Using temp tables cause bloat so keep at this until you/we identify the problem.
0
SteveL13Author Commented:
Pat,

No captions in any tables.
0
SimonCommented:
If you use the same SQL statement in a simple select query (in the query designer) and then view the results as datasheet and try to filter or sort on the [project name] column, do you get the same problem?

If so, consider aliasing the column as ProjectName:[Project name] - it shouldn't make any difference, but I'm always a little suspicious anywhere that a reserved word is used in column names.

Final thought for the day. Is that column involved in a relationship? Is it the primary key for the Projects table, or is there a numeric autonumber PK on the table?
0
hnasrCommented:
Why not upload a sample database demonstrating the issue?
0
SteveL13Author Commented:
Thank you to all that replied to my post.  I have decided to use my make-table solution.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
Thank you to all that replied.  I have decided to use my own make-table solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.