Using variables as column names

[GerryGoldberg]

I would like to create a SELECT or UPDATE query where the column name is supplied as a variable. Likewise, I would like to use this variable in a CAST or CONVERT functions. I am using SQL-7 with Access 97. The SQL syntax description for the query does not indicate that this is possible. Is there any way I could do this.

Thanks,

Gerry Goldberg

 

[kathryn]

If I understand you correctly, you can do this pretty easily through code. In your code, you build the SQL query string dynamically, using your variable names, like this:

-------------------

dim strSQL as string

strSQL = "SELECT " & VariableName & " FROM TableName"

docmd.runsql strsql

-------------------

Is this what you are trying to do? Kathryn

 

[GerryGoldberg]

Yes, except that I had hoped to do it as a "Trigger". Any ideas?

Thanks,

Gerry Goldberg

 

[kathryn]

You want this to run as a Trigger in SQL? So you are trying to dynamically create a Trigger from MS Access?? I have never heard of this being done.

If I have misunderstood you, let me know and I will keep working on it. Kathryn

 

[GerryGoldberg]

Actually, I'm not trying to dynamically create the Trigger. I'm just trying to dynamically construct an UPDATE query in the trigger code. Now, after lot's of e-mails and advice, I've found that I can do just that by using SQL's "Execute" command, or the "sp_executeSQL" command. So, many thanks for the help and encouragement to you and to everyone else who responded to my question!

Gerry Goldberg