Access: Syntax Error

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
I could have sworn that this code worked a couple of months ago but now I am getting the following error when attempting to run it:

Code:
Syntax error (missing operator) in query expression 'CASE WHEN d.SALESDAT IS NULL    THEN ' '
    ELSE TO_CHAR(d.SALESDAT,'MM/dd/yyyy')
END'.

After selecting OK, "WHEN" is highlighted.

Here is the beginning of my code to help put things into perspective:

Code:
SELECT 
d.PRODID AS Subsidiary_Identifier, 
d.PRODID2 AS Machine_Type, 
d.SERIALNO AS Mach_NO,
CASE WHEN d.SALESDAT IS NULL
    THEN ' '
    ELSE TO_CHAR(d.SALESDAT,'MM/dd/yyyy')
END AS Commissioning_Date,
CASE WHEN e.CDSTART IS NULL
    THEN ' '
    ELSE TO_CHAR(e.CDSTART,'MM/dd/yyyy')
END AS Date_of_Service_Work,
e.NO_INT AS Service_Worksheet_No, 
CAST(i.DOMVALMNEMO AS VARCHAR(10)) || ' - ' || ISNULL(h.DESCRLONG,' ') AS Error_Code, 
d.ERRTXT AS Error_Reported_by_Customer, 
f.ORDTXT AS Error_Reported_work_performed, 
ISNULL(j.NO_INT,' ') AS Mat_no_of_offending_component,
a.NO_INT AS Parts_Used,
a.nam AS Description,
e.empliddispo AS TechnicianID

Does anyone know what is causing this error? I believe this is using ORACLE or PSQL but really don't know. I am a beginner with Access and running queries.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
try using he NVL2 function instead of
case when else

https://www.techonthenet.com/oracle/functions/nvl2.php

if that doesn't work try returning NULL instead of ' '
if that doesn't work try returning a hard coded date instead of ' '

Thanks for the reply. I can't see the data yet because of another error (shocking), but it looks like using the NVL2 function got rid of that syntax error. Unfortunately now it is giving me an error for the line of code a few lines down:

Code:
CAST(i.DOMVALMNEMO AS VARCHAR(10)) || ' - ' || ISNULL(h.DESCRLONG,' ') AS Error_Code,

It highlights "AS". Any ideas what is going on with this one?

It is so weird that this worked fine (to the best of my knowledge) a few months ago but doesn't work now...
 
Upvote 0
Bump. Anyone have any ideas here? Beginning of code is now

Rich (BB code):
SELECT 
d.PRODID AS Subsidiary_Identifier, 
d.PRODID2 AS Machine_Type, 
d.SERIALNO AS Mach_NO,
NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') AS Commissioning_Date,
NVL2(e.CDSTART,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') AS Date_of_Service_Work,
e.NO_INT AS Service_Worksheet_No, 
CAST(i.DOMVALMNEMO AS VARCHAR(10)) || ' - ' || ISNULL(h.DESCRLONG,' ') AS Error_Code, 
d.ERRTXT AS Error_Reported_by_Customer, 
f.ORDTXT AS Error_Reported_work_performed, 
ISNULL(j.NO_INT,' ') AS Mat_no_of_offending_component,
a.NO_INT AS Parts_Used,
a.nam AS Description,
e.empliddispo AS TechnicianID

and I get a syntax error on the red line highlighting "AS". Thank you james_lankford for helping me fix the previous lines.
 
Upvote 0
What happens if you simply remove As Error_Code?
 
Upvote 0
What happens if you simply remove As Error_Code?

Thanks for the reply Norie. My mistake for not specifying. It highlights the first "AS" in that line of code. To answer your question though, when I delete "AS Error_Code", I get the same syntax error highlighting the first "AS".
 
Upvote 0
If you remove all the AS ... parts does it work but give you 'random' aliases for the columns you were trying to name?

Does it make a difference if you enclose the names, eg Error_Code, in single/double quotes?
 
Upvote 0
If you remove all the AS ... parts does it work but give you 'random' aliases for the columns you were trying to name?

Does it make a difference if you enclose the names, eg Error_Code, in single/double quotes?

If I remove the first "AS", I still get the same syntax error but this time highlighting "VARCHAR(10)". If I remove "AS VARCHAR(10)", I get an error saying invalid use of vertical bars.
 
Last edited:
Upvote 0
Is this definitely a query you are trying to run in an Access database?
 
Upvote 0
If you remove all the AS ... parts does it work but give you 'random' aliases for the columns you were trying to name?

Does it make a difference if you enclose the names, eg Error_Code, in single/double quotes?


yeah,try that

try changing every line that has an AS
NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') AS Commissioning_Date,

try these different forms for each line
NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') Commissioning_Date,

NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') [Commissioning_Date],

NVL2(d.SALESDAT,TO_CHAR(d.SALESDAT,'MM/dd/yyyy'),' ') 'Commissioning_Date',
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top