x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 3 replies
  • Answers 2 answers
  • Subscribers 6 subscribers
  • Views 609 views
  • Users 0 members are here
Browse Forums
Announcements
678
topics
29
replies
Financial year end
60
topics
141
replies
General Discussion UK
7k
topics
17k
replies
General Discussion Ireland
136
topics
211
replies
Install your software
99
topics
282
replies
Making Tax Digital
281
topics
676
replies
Manage your VAT
97
topics
206
replies
Protect and repair your data
27
topics
94
replies
Run your accounts remotely
70
topics
169
replies
Service Alerts
1
topics
0
replies
Working with the bank
72
topics
196
replies
J Rowlan

Sage 50 Accounts v28.1 problems using Sage ODBC DSN to collect Inter Comapny Balance transfers

SUGGESTED
Posted By

I use Excel spreadsheets to manage data relating to intercompany Balance Transfers.

In v25 this code worked just fine.

SELECT AUDIT_JOURNAL.RECORD_CREATE_DATE AS Posted,AUDIT_JOURNAL.TRAN_NUMBER, AUDIT_JOURNAL.TYPE, AUDIT_JOURNAL.DATE, AUDIT_JOURNAL.ACCOUNT_REF, AUDIT_JOURNAL.INV_REF, AUDIT_JOURNAL.DETAILS, AUDIT_JOURNAL.TAX_CODE, AUDIT_JOURNAL.AMOUNT
FROM AUDIT_JOURNAL AUDIT_JOURNAL
WHERE (AUDIT_JOURNAL.NOMINAL_CODE=1110)
and deleted_flag=0
ORDER BY AUDIT_JOURNAL.TRAN_NUMBER

however in 28.1 the query above returns no new records for the current year (2021-2022) on any of my 3 trading company Sage instances.

Its as if there is a cut off point at or around the 31st Mar 2022

I suspected that the cause was down to a change in Sage 28.1 and that perhaps the tables that I am querying via DSN have been changed or a different method has been introduced to provide the same information.

I have ensured that the DSN in use are 32bit and for v28.1 (although I don't think there is much difference between 28 and 28.1

is this a known issue?

thanks all,

jON

Parents
  • 0
    SUGGESTED

    We do not actually support direct consumption of the ODBC driver, only it's use through Report Designer and Excel Integrated Reporting. There have been no changes to the AUDIT_JOURNAL table though. As the only filter you have is for a specific nominal code and the deleted flag, and no date filter at all, I would hazard a guess that the DSN you are using is not connected to the same data as you are looking at in Accounts. Worth trying to create a simple report on AUDIT_JOURNAL using Report Designer and confirming that you do in fact see data for 2022-2023 in that.

Reply
  • 0
    SUGGESTED

    We do not actually support direct consumption of the ODBC driver, only it's use through Report Designer and Excel Integrated Reporting. There have been no changes to the AUDIT_JOURNAL table though. As the only filter you have is for a specific nominal code and the deleted flag, and no date filter at all, I would hazard a guess that the DSN you are using is not connected to the same data as you are looking at in Accounts. Worth trying to create a simple report on AUDIT_JOURNAL using Report Designer and confirming that you do in fact see data for 2022-2023 in that.

Children