How to use expression for field name in query?

Question

hi

i'd like to use an expression as a field name. How is this done?

Example:

SELECT MyField AS IIf (True, "Fieldname1", "Fieldname2") FROM MyTable

So far, i'm just seeing the full text of the function as the fieldname, instead of the result of the function. 

thx!

Answer 1

That is not possible. Field names in a query are fixed.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

SELECT MyField AS IIf (True, "Fieldname1", "Fieldname2") FROM MyTable

Hi johny,

You could construct an sql_string like:

    sql_str = "SELECT MyField AS " & IIf (True, "Fieldname1", "Fieldname2") & " FROM MyTable"

and use that string as RecordSource in a form, or assign it to the SQL property of a QueryDef, or whatever you want.

Imb.

Answer 3

SELECT MyField AS IIf (True, "Fieldname1", "Fieldname2") FROM MyTable

Hi johny,

You could construct an sql_string like:

    sql_str = "SELECT MyField AS " & IIf (True, "Fieldname1", "Fieldname2") & " FROM MyTable"

and use that string as RecordSource in a form, or assign it to the SQL property of a QueryDef, or whatever you want.

Imb.

Nice solution!

I want to construct my queries in the query designer, so your approach won't quite work. I figured out the solution for me:

I will create the query in query designer, and put the fieldname expression right in the query designer (as in the OP). Then, at execution time, my VBA code will parse the expression, 'rewrite' the query, then execute. 

It could be executed as a new query def, or as a recordset, or maybe other ways. 

thx!

Answer 4

That is not possible. Field names in a query are fixed.

Thx for the info, Hans. It would be nice, but will have to get creative instead. 

cheers!