Trying to take use variables in Access Jet SQL, possible?

I'm trying to select a value from a table, but I'm trying to use a value from another table to tell which column to use. So basically I'm trying to use a variable. Here's what I'm trying to do:

SELECT TABLE2.(TABLE1.COL1) FROM TABLE2

What this is saying is that the value for table1.col1 is a heading for table2. Can I do this in Access via SQL?
bemara57Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
GRayLConnect With a Mentor Commented:
To clarify the answer from lowfatspread:

Select IIF(table1.col1="fld1",table2.fld1) FROM table1,table2 ON table1.ID=table2.ID;

There has to be some value (ID?) in table2 which restricts table1 to a single record.  This any clearer?
0
 
LowfatspreadCommented:
you need to code it as an IIF i believe

e.g.  Select IIF(table1.col1 = 'x',X,Y) from table2
0
 
mbizupCommented:
You can use VBA...

strSQL = "SELECT Table2." & dlookup("Col1", "TABLE1", "SomeField = SomeValue") & " FROM Table2"
                                                                                                ^^^-- criteria, if needed

The concatenated dlookup will allow for a variable field name.
This sql string can then be used in a recordsource, rowsource, etc.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
stevbeCommented:
you can use DLookup directly in a query, no need to use VBA.
0
 
stevbeCommented:
how do you know which record in table1 to use?
0
 
LowfatspreadCommented:
thanks GRayL

0
All Courses

From novice to tech pro — start learning today.