[RESOLVED] Use variable to append field parameter

[mantooth29]

Cross posted here:
http://stackoverflow.com/questions/1...ield-parameter

I am using ADO to retrieve data from MS Access into Excel, and I want to append an Iif statement into the SELECT part of my query. My SQL string is stored in a text file, and any parameters are being appended via ADO.

I know how to append parameters in the WHERE clause, and can even append parameters in the SELECT statement like this:

SELECT BA, Iif(QA=[@somestring],[@somestring2], [@somestring3])
FROM myData
WHERE BA = 'some person'

But what I am trying to accomplish is to transform a placeholder [@somestring] into a variable length Iif statement, such as:

SELECT BA, [@somestring]

And the placeholder translates to a string I have built using a function:

Iif(QA='Jon Doe', 'Jon', Iif(QA='Jane Doe', 'Jane', 'Nobody')).

Right now, the result I get is the literal string I pass to the parameter populating the entire field. So the Iif statement is literally written out for each record, rather than being evaluated by SQL.

Is what I am trying to do even possible, or does SQL need the Field for the Iif statement predefined in the initial command?

ie,

Iif(QA=[@somestring], [@sometstring2], [@somestring3])

[si_the_geek]

It isn't possible, you can only use parameters to set Values, you can't do other things with them (such as field/table names, functions, etc).

If you want to alter the other parts of the query, you'll need to use other methods (such as string building). In the example at the end of your post, you should add that to the query string, and add the values via parameters.

[mantooth29]

Ok Thank you.

I have put my recent efforts into eliminating string building in my SQL statement to keep my modules clean. That is why I am using text files and appending parameters.

However, the function I am using builds a variable length Iif string (varying levels of nested statements).
I will focus on inserting the Iif string itself into the text file, and then running the query from there.

Thanks for saving me more time spent chasing my tail.