External data query - MS query parameter limits

[cr731]

I am trying to use the Microsoft Query to retrieve data from an external program and I am having an issue with the quantity of parameters I need to incorporate. Basically, I have about 100 values (i.e. accounts) that I need to be able to independently specificy. I've done this by having four separate criteria lines such as

In([U1],[U2],[U3]...)

It could not fit all 100 on one criteria line, so I've created four lines with 25 accounts each. That works okay, but, when I then try to add another column of critieria, say to specify the year or a subaccount, I get an error:

SQL0104 - Token [ was not valid. Valid tokens : ( + - ? DAY NOT RRN CASE CAST CHAR DATE DAYS HASH HOUR LEFT

I can only assume I am receiving this because of some sort of size limit... Is anyone familiar with MS Query's limits to the number of parameters that can be used? Or is there some more efficient way of specifying that a parameter must be one of a longer list?

Thanks

 

[Fazza]

hi,

I don't know the limits of MS Query: AFAIK it is fine for basics & won't do everything.

One approach might be using VBA to control the query. Such as worksheet change event to monitor cell changes for the parameter inputs. Via VBA modify the query's SQL. I understand the SQL limit is in excess of 65,000 characters. So, 100 or so parameters should be fine. Create the SQL of form,

SELECT whatever
FROM table
WHERE somefield IN ('your','list','even','if','there','are','many','values')
AND whatever other criteria in normal SQL

There are two critical properties of the query table object. The Connection and the CommandText. The CommandText is also known as SQL. So, you can use qt.SQL = "revised SQL string" to set new SQL for a table. Then simply refresh.

OK?

If you have a simple list in a table, another way is to include that in the SQL instead of the IN ('big,'list') such as,

SELECT A.*
FROM tableA A, tableofcriteria C
WHERE A.somefield = C.somefield

This is the old way of writing SQL: equally can be an inner join.

HTH. regards