Can i execute query in iif function

Question

I want to know can i run a query in iif function used in ms access database. My case

Select field1,(iif(3<4,'Select * from tbl1','select * from tbl2')) from tblmain

I am facing syntax error when i try to executed query like that whats the problem

Answer 1

What you're trying to achieve isn't clear from your sample query.

You can use IIF functions in Access queries, for example:

SELECT IIF([SomeField]<15, "Smaller than 15", "Greater than!") As Whatever 
FROM myTable

You can use subselects in Access as well, for example (example shamelessly stolen from http://allenbrowne.com/subquery-01.html):

SELECT MeterReading.ID, MeterReading.ReadDate, MeterReading.MeterValue, 
    (SELECT TOP 1 Dupe.MeterValue                 
     FROM MeterReading AS Dupe                     
     WHERE Dupe.AddressID = MeterReading.AddressID 
         AND Dupe.ReadDate < MeterReading.ReadDate   
     ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue 
FROM MeterReading;

Note that the specified subselect query must be guaranteed to return a single record - either by specifying TOP 1 or using an aggregate function - and must link back to the parent query in the WHERE clause.

You can't use an IIF statement the way you're trying to in your question, however, even if your subselect was valid, which it is not.

Two options to suggest, although it is less than clear to me what you're trying to achieve here. First, you might want to consider doing it in VBA instead. Something like:

const query1 As String = "Select * from tbl1"
const query2 As String = "select * from tbl2"

Dim recset as DAO.Recordset
set recset = CurrentDB.OpenRecordset(iif(3<4, query1, query2))

Alternatively, if both tbl1 and tbl2 had the same fields you could do something like this:

SELECT * FROM tbl1 WHERE 3<4
UNION ALL
SELECT * FROM tbl2 WHERE NOT (3<4)

If you replace 3<4 by whatever actual condition you're checking for, you'll only get back records from one or the other or query, never both. However, my suspicion is that if you need to do this, your database may have design issues - I can think of many questionable scenarios where this would be needed, and few valid ones, although I'm sure they exist.