Syntax Error in Expression

Question

I am having issues with Syntax errors in expressions when there are no expressions (or dates) present...

I imported tables only to a new database in case of corruption. Working to re-design queries.  I test run a query, and it works.  I close it, move on to the next query, which combines query 1 with another table.  When designing the query with relationships in place, I drag a field down to the query (no expression).  Click on run, and I get this error message:  Syntax Error in Query Expression 'Assessment_Name'.  There is no expression.  If I delete the 'Assessment_Name' (Related field), save the query, add the 'Assessment_Name' field back in, save, hit run, I am able to see the list of assessment names in my table, and choose a specific name.  The next time I try to run the query, I get the syntax error message again.

It's not just with this field name; sometimes I get the error message in reference to a standard number field that is calculated in a table, and works great, but then the syntax error shows syntax error in date expression.  THERE IS NO DATE!  I'm multiplying by .01. 

I also get the date error in reference to my Question# field (See screen snips below).  Question number records range from 1 - 2500, with digits repeated, based on the assessment name.  I changed the format from double to long integer; it makes no difference.   I still get the date error message.

Please help.  I have re-created my database so many times, but always get the same errors in my queries, when there are no expressions; no VB code.  I don't know what to do.

This is the access version I'm running: 

Answer 1

First, don't use special characters in fields names like #. Also are youu actually using underscores or are their spaces in your object names?

Second, please post the SQL for your query.

Answer 2

I changed # to No; they are underscores, and here's the code:

SELECT Questions.Assessment_Name, Questions.Question_No, Questions.Group

FROM Questions INNER JOIN Correlations ON (Questions.Assessment_Name = Correlations.Assessment_Name) AND (Questions.[Question_No] = Correlations.[Question_No]);

Answer 3

Are there any Nulls in either Assessment_Name or Question_No?

Personally I would just use the ID field from Questions as a Foreign key in Correlations.

Answer 4

no nulls.

Not sure what you mean by using the ID field as a foreign key?