Variable for Column Name in SQL Statement

[ptuck]

I am trying to pull data from an access database based on the user logged in. I have set a variable for the column name, but of course when I plug this in to my SQL statement it views it as variable instead of a column name...imagine that. Here is the statement.

SQLName = "SELECT Name FROM tblNames Where '" & User & "' <> '" & ID & "' ORDER BY Name"

The '"&ID&"' is what I want to be the column name (example of column name is UserID1 - UserID16). Is there anyway to do this? Say something like "If ID = Column UserID 10 Then the SQL statement could use the column number instead of the name. Does this make sense. In other words, can I tell the SQL statement to compare X against Column Number instead of the actual Column Name?

Thanks,
Paul

 

[onpnt]

column names in SQL are not surrounded by ' '
e.g.
Where " & User & "
just take those out and it should work out.

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[ptuck]

If I remove the quotes from it does not know the Column name ID ( '" & ID & "' is the one that I have stored the actual column name in). The column names are UserID1 - UserID 16, but I determine what column is used by the user logged in. This is why I am storing the column name in the variable. I go through an IF Statement to see who is logged in then assign the column name for that user. Hope this makes sense. Also, this is an Access DB if it makes a difference.

Paul

 

[SnaveBelac]

I think it should be like this

SQLName = "SELECT Name FROM tblNames Where " & columnVariableName & " <> '" & stringValue & "' ORDER BY Name"

Obviuosly you would have to replace columnVariableName & stringValue with your variable names.

----------------------------
SnaveBelac - Adventurer
----------------------------

 

[onpnt]

Did you try it??

SQL no matter Jet, TSQL, ANSI...or whatever construct foes not expect strings for column headers in any query that I know of

At this point I would say go to your M$ Access database and hit the query tools with what you are outputting here.

teh proper JETSQL syntax for a select no matter is dynamically included in the statement IS

SELECT column
FROM table
WHERE
column = something
OR/AND
column = something
ORDRE BY column

In the most simple form of a select!

so in your example you need to generate
SELECT [Name] FROM [tblNames] Where UserID1 <> ID ORDER BY [Name]

NOTE: another syntax issue in SQL. data types! ID?!? is is a text, number blah, blah...etc...

If that doesn't help or you don't want to try it then I suggest some SQL tutorials (JETSQL) or our great FAQ resources here.

Good Luck!!!

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page