Solved

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

Posted on 2007-03-23
6
274 Views
Last Modified: 2008-02-01
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
Comment
Question by:bemara57
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18781719
you need to code it as an IIF i believe

e.g.  Select IIF(table1.col1 = 'x',X,Y) from table2
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18781771
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
 
LVL 39

Expert Comment

by:stevbe
ID: 18782003
you can use DLookup directly in a query, no need to use VBA.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 39

Expert Comment

by:stevbe
ID: 18782007
how do you know which record in table1 to use?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 18783252
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18784591
thanks GRayL

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question