Unanswered: Query Returns Different Results in Access vs Excel?
I have a very simple query which returns activity related to a certain product code.
I had realized that certain undesirable test accounts had been creeping into the query, so I isolated them into their own query with the intent of filtering them out of the result set with an outer join. This is where the problem lies.
This this query in Access correctly recognizes when AccountIDs in TestAccts match the AccountID in sysdba_SALESORDER, and will show the accompanying AccountID and AccountName in Access.
My Excel workbook has a table which is linked to this query, but AccountID and AccountName are blank in all cases in this query, even when the AccountID is showing as matching when the same query is run through Access.
Any ideas or help would be appreciated. I was expecting this to be a 5 minute fix and it's taking me all afternoon.
SELECT sysdba_ACCOUNT.ACCOUNT, sysdba_SALESORDER.PRODUCTACCESSTYPEPATHWAY, sysdba_SALESORDER.MODIFYDATE, sysdba_SALESORDER.CREATEDATE, sysdba_SALESORDER.ORDERTYPE, sysdba_SALESORDER.STATUS, sysdba_SALESORDER.GRANDTOTAL, TestAccts.ACCOUNTID, TestAccts.ACCOUNT
sysdba_SALESORDER INNER JOIN sysdba_ACCOUNT
sysdba_SALESORDER.ACCOUNTID = sysdba_ACCOUNT.ACCOUNTID
) LEFT JOIN
ON sysdba_ACCOUNT.ACCOUNTID = TestAccts.ACCOUNTID
Contents of TestAccts:
SELECT sysdba_ACCOUNT.ACCOUNTID, sysdba_ACCOUNT.ACCOUNT
WHERE (((sysdba_ACCOUNT.ACCOUNT) Like ("*Test *")));
Please excuse the bump, but I am still having problems getting these two query results to be the same in Excel and Access.
In the past, I have found Excel errors in data connections to Access queries which parse null values in some way. It surprises me that the above query would not work correctly, as I am not addressing nulls in this query. I would love to understand more about the discrepancies between how Excel and Access interpret the same query, as I use this functionality quite a lot and am alarmed to see buggy results like this.