Syntax of an SQL querystring

[j_Southern]

I need to query a database selecting two fields whose names vary by a number depending on the month.
I have used MSQuery to give me an SQL statement and I am trying to modify this so that I can choose the month whose data I want.
I have ended with this which does not work.

Please Login or Register  to view this content.

n is a number from 1 to 12 which enables me to choose. I have not got the sequence of quotes around n right. I am also worried about the single quotes around the ACTUAL_LAST_YEAR#
And ACTUAL_MONTH# as they don't appear in ACCOUNT_NUMBER or ACCOUNT_NAME. (They were in the MSQuery sql string)
I hope somebody will help me get the right syntax
John

[CK76]

Try something like below...

Please Login or Register  to view this content.

[j_Southern]

CK76 Thanks for your interest. I can see now that writing "n" was daft. However your suggestion is still not working. I get a compile error at:-
[CODE]SQLStr = SQLStr & "NOMINAL_LEDGER.'ACTUAL_MONTH#" & n & ' "/CODE]
John
ps I have just seen that I missed a " from your code. I will try again!

[j_Southern]

CK76 I have tried your suggested code but having overcome all the compile errors I have now hit a problem on the next stage.

Please Login or Register  to view this content.

The last two lines which are alternatives I have found are sometimes needed. But line 10 give the Application defined or Objectdefined error and line 10 gets "Object variable not set" error
I am not sure if an incorrect SQL statement would give either of these errors. I'll test that by simplifying things. But have you any suggestions?
John

[j_Southern]

My testing a very simple form of the SQLStr with both lines 10 or 11 of the ODBC sub resolved nothing. I did realise however that I had missed off a line from the SQL
SQLStr = SQLStr & "FROM ACCOUNTING_SYSTEM.NOMINAL_LEDGER NOMINAL_LEDGER"
This did not prove to be the answer either.I hope you can see where I am going wrong.
John

[CK76]

I don't typically use VBA with Query Table (I prefer ADODB, or using PowerQuery directly).

But the error raised, has nothing to do with query string.

Remove line 10 & 11... then try using qtSage object (i.e. query table that you defined already) to refresh etc.
ex:

Please Login or Register  to view this content.

[j_Southern]

When I am using our SQL database I also use ADODB in preference. In this application I have to refer to an older data set associated with Sage Line100 as well. I don't think I can use ADODB with it.
When I replace lines 10,11 with your new line a very strange thing happens. I am asked to select a machine data source from the list of data sources on my machine, and then I am asked for username and password. It is as if the line

Please Login or Register  to view this content.

is being ignored.
When I select the data source and provide the password I get an unhelpful error message "General ODBC error"
I am exploring further the possibility of using ADODB, but have you any further ideas?
John

[CK76]

My best guess, is that your connection string may be missing parameters. Or the sql syntax is not permitted for Sage line100 (from what little documentation I can find, it uses ODBC v2 an older version)... so it may have to do something with that.

Unfortunately, I don't have Sage Line100 to test with.

There's very little documentation on Sage Line100 that I can find...
Perhaps one of below links may help.
https://andrewonedegree.wordpress.co...c-driver-hmmm/
https://support.na.sage.com/selfserv...1211&sliceId=1
http://timandpaige.com/technology/sa...2013-odbc-help