SYNTAX ERROR when INNER JOIN (1 Viewer)

[Bosve]

Hi, I am having the following issue with the exact same SQL query

Window7 + Access 2013: INNER JOIN query works without issues on SQL tables

Windows 10 + Access 2016: exactly the same query: SYNTAX ERROR when INNER JOIN on SQL tables all other queries work fine.

 

[June7]

Cross post https://www.accessforums.net/showthread.php?t=79010

Provide query SQL statement.

 

[Bosve]

Provide query SQL statement.

SELECT DISTINCT BATCHSERIES.BATCHSERIESNO, STOCKTABLE.ITEMNAME, BATCHSERIES.BESKR1, BATCHSERIES.BESKR2, BATCHSERIES.BESKR3, BATCHSERIES.BESKR4, STOCKTABLE.ITEMNAME2UK, STOCKTABLE.ITEMNAME3UK, StockUnitConvert([STOCKUNIT]) AS Unit FROM BATCHSERIES INNER JOIN STOCKTABLE ON (BATCHSERIES.ITEMNUMBER = STOCKTABLE.ITEMNUMBER) AND (BATCHSERIES.DATASET = STOCKTABLE.DATASET) WHERE (((BATCHSERIES.STOCKSTATUS)=3) AND ((BATCHSERIES.DATASET)='AAB')) UNION SELECT '','********','','','','','','','' FROM BATCHSERIES

ORDER BY BATCHSERIES.BATCHSERIESNO;

 

[June7]

Only thing I can think might be issue is asterisk characters which are wildcard in Access SQL. Why use these characters?

 

[Cronk]

asterisk characters which are wildcard in Access SQL

That would apply in a WHERE Clause but not in a SELECT field string.

 

[June7]

Good point.

Still wonder why using asterisk characters.

I always use Null instead of empty string in UNION. That way not mixing number or date fields with empty string value.

 

[CJ_London]

Would also help to know the exact wording of the syntax error.

Some thoughts

You don't need SELECT DISTINCT, just SELECT - a straight union query automatically use DISTINCT

perhaps the error is in your function StockUnitConvert

By SQL tables, I presume you mean SQL Server? In which case are your ODBC drivers correct?

 

[The_Doc_Man]

'[COLOR="Red"](1)[/COLOR]' ,'********' [COLOR="red"](2)[/COLOR], '[COLOR="red"](3)[/COLOR]', '[COLOR="red"](4)[/COLOR]', '[COLOR="red"](5)[/COLOR]', '[COLOR="red"](6)[/COLOR]','[COLOR="red"](7)[/COLOR]','[COLOR="Red"](8)[/COLOR]','[COLOR="red"](9)[/COLOR]'

I added the numbers for clarity. The above, originating from the second part of the UNION, returns 9 fields, ALL of which are of type Text (because of the quoted strings.) The first part of the UNION also returns 9 fields, so that much is OK. However, are all of the field types in the first part of the UNION compatible? If anything other than the first field is numeric, you might have a type mismatch issue to consider.

EDIT: Revisited what was posted and realize I misread the first field. It, too, is a zero-length string. If ANY of the fields from the first SELECT in the UNION return a number, you have a type mismatch, but the UNION clause complicates matters and might lead to a SYNTAX error. I'm not sure what would happen there.

 

[Bosve]

Thank you for all the replies and I appreciate the time you took to understand my problem but your answers are currently too advanced for me. This Access application was not developed by me so I don't know in detail how it works. I was hoping for a quick solution like something obvious I am missing.

However one of you suggested ODBC drivers as a root cause which is very possible since I have issues with Excel documents that are fetching data from the same SQL DB as Access. Excel documents work flawlessly in W7/Office2013 but fail when opened in W10/Office2016 environment.