[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

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?
0
bemara57
Asked:
bemara57
1 Solution
 
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
 
stevbeCommented:
you can use DLookup directly in a query, no need to use VBA.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
stevbeCommented:
how do you know which record in table1 to use?
0
 
GRayLCommented:
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:
thanks GRayL

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now