SYNTAX ERROR when INNER JOIN (1 Viewer)

Bosve · Dec 1, 2019

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 · Dec 1, 2019

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

Provide query SQL statement.

Bosve · Dec 1, 2019

June7 said:
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 · Dec 1, 2019

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

Cronk · Dec 2, 2019

asterisk characters which are wildcard in Access SQL

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

June7 · Dec 2, 2019

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 · Dec 2, 2019

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 · Dec 2, 2019

Code:

'[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 · Dec 3, 2019

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.

SYNTAX ERROR when INNER JOIN (1 Viewer)

Bosve

Registered User.

June7

AWF VIP

Bosve

Registered User.

June7

AWF VIP

Cronk

Registered User.

June7

AWF VIP

CJ_London

Super Moderator

The_Doc_Man

Immoderate Moderator

Bosve

Registered User.

Similar threads

Users who are viewing this thread