Invalid Syntax error - Access 365 - Update to the Issue (1 Viewer)

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
I have been writing Access queries for about 30 years and I just can not figure out what the issue is. This exact same query works just fine in Access 2010.

Here is a simple example of where I am getting the error:

Tot Rep Billed to Date: iif(IsNull([Qry Sum Total replaced Billed to Date by Prin Project Pillar].[Total Rep Billed to Date], 0, [Qry Sum Total replaced Billed to Date by Prin Project Pillar].[Total Rep Billed to Date])

The query "Qry Sum Total Replaced Billed to Date by Prin Project Pillar" is included in the tables on the top half of the screen. The Invalid Syntax error highlights the third parameter of the iif statement. I also tried it without using the explicit query name in front of the field name. Neither works.

What am I missing? Are my query and field names too long? They have never been before!

Thank you in advance!
Joanne
 

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
One middle parenthesis?
 

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
Ha! I see that now, thanks Isaac.

But even when I fix that, I still get the same error on the third parameter:

Tot Rep Billed to Date: iif(IsNull([Qry Sum Total replaced Billed to Date by Prin Project Pillar].[Total Rep Billed to Date]), 0, [Qry Sum Total replaced Billed to Date by Prin Project Pillar].[Total Rep Billed to Date])
 

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
Do you get the error when you first Tab out of the field you are typing in, or later when the query runs?
 

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
Hmm. I cannot reproduce your error. (see attached).
I'm also on 365
 

Attachments

  • Testing 20200707.accdb
    404 KB · Views: 115

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
Hey Isaac. When I switch to SQL code and have the correct syntax (which I didn't in the post I deleted), I do not get an error. I wonder if there is a bug with the Query Builder tool in Access 365? I haven't seen any posts about it but it seems to be the Builder and not my SQL code.
 

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
If you didn't get the error when first tabbing out of the field, but DID get this error you pictured, then I would say it was entirely possible that the SQL-view error was baloney, and there was an error somewhere else in the SQL (as often, for me, the SQL-view Run error is, more interesting perhaps, but totally wrong, and has something else to do with syntax or parenthesis).

But given that you get the syntax error immediately upon tabbing out of the field when typing that expression, I'm stumped. Perhaps someone who is more skilled than I at eyeballing raw SQL in Access's hideous IDE will come along and straighten us out! :)
 

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
Ok I'm confused now. So you do NOT get the error when tabbing out of the field after typing the expression, when using the corrected syntax?
 

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
Sorry to confuse. I switched to SQL view (not the Query Builder) and when I write the expression from there I do not get an error message.

In other words I ONLY get the error when I tab out of the field in the Query Builder.
 

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
I cannot think of a reason. Weird. Maybe someone else has experienced a cause for this and will shed some light. I mean, your object names ARE really long, and maybe that's what makes it choke--not sure I've ever had a query name that long, so could be that's why I haven't experienced it. Not sure though.
 

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
Thanks for your time Isaac.

Let's see if anyone else out there sees something.....
 

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
I have been playing with this and here is the bottom line. If I use the SQL shown below in the SQL View of Access 365, it runs absolutely fine. As soon as I am in Design View (query builder) and edit anything within the field with the iif statement expression, I get a Syntax Error when I tab off the expression field.

Here is the SQL code:

SELECT [Entrepreneur Company].[Company Name], [Entrepreneur Company Principal Projects].[Replacement Status], IIf(IsNull([Qry Sum Total Replaced Billed to Date by Prin Project Pillar].[Total Rep Billed to Date]),0,[Qry Sum Total Replaced Billed to Date by Prin Project Pillar].[Total Rep Billed to Date]) AS [Total Billed]
FROM (([Entrepreneur Company] LEFT JOIN [Entrepreneur Company Programme Years] ON [Entrepreneur Company].[Entrepreneur Company ID] = [Entrepreneur Company Programme Years].[Entrepreneur Company ID]) LEFT JOIN [Entrepreneur Company Principal Projects] ON [Entrepreneur Company Programme Years].[Entrepreneur Company Programme Years ID] = [Entrepreneur Company Principal Projects].[Entrepreneur Company Programme Years ID]) LEFT JOIN [Qry Sum Total Replaced Billed to Date by Prin Project Pillar] ON [Entrepreneur Company Principal Projects].[Principal Project Pillar ID] = [Qry Sum Total Replaced Billed to Date by Prin Project Pillar].[Principal Project Pillar ID]
WHERE ((([Entrepreneur Company Principal Projects].[Replacement Status])="Replaced"));
 

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
Can you post a copy of your db? Maybe stripped down to nothing but the tables/queries involved in that query, even with zero data in the tables?
 

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
Here is the DB with only the necessary tables and queries. The query in question is JB Tester
 

Attachments

  • Expression Error Test.accdb
    1.7 MB · Views: 119

Isaac

Lifelong Learner
Local time
Today, 06:17
Joined
Mar 14, 2017
Messages
8,738
I actually didn't get any error when tabbing out of the Total Billed column. I even tried deleting a letter and replacing it, to sort of Dirty the situation, then tab out - no error. Sorry I could not reproduce it. :(
 

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
I actually didn't get any error when tabbing out of the Total Billed column. I even tried deleting a letter and replacing it, to sort of Dirty the situation, then tab out - no error. Sorry I could not reproduce it. :(
Wow..... I wonder if there is something I didn't install when I installed Access. I am using the newest version via Microsoft 365. What version are you using?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:17
Joined
Jul 9, 2003
Messages
16,244
The query works OK for me in both views, SQL and the query builder grid. I have added and deleted characters from the iif statement and and it works OK. If you would like to explain how I can reproduce the problem I can double check...
 

FrHollow

New member
Local time
Today, 09:17
Joined
Jul 7, 2020
Messages
11
The query works OK for me in both views, SQL and the query builder grid. I have added and deleted characters from the iif statement and and it works OK. If you would like to explain how I can reproduce the problem I can double check...
What version of Access are you using? It works fine for me in Access 2010 but not with the latest version via Microsoft 365. I get the error after I change anything within the expression, even the label, and click off the field or press Tab.
 

Users who are viewing this thread

Top Bottom