我可以在 iif 函数中执行查询吗

发布于 2024-10-20 14:51:54 字数 197 浏览 1 评论 0原文

我想知道我可以在 ms access 数据库中使用的 iif 函数中运行查询吗？我的情况是，

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

当我尝试执行这样的查询时，我遇到语法错误，问题是什么

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

宁愿没拥抱 2024-10-27 14:51:54

从示例查询中并不清楚您想要实现的目标。

您可以在 Access 查询中使用 IIF 函数，例如：

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

您也可以在 Access 中使用子选择（例如，无耻地从 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;

请注意，必须保证指定的子选择查询返回单个记录 - 通过指定 TOP 1 或使用聚合函数 - 并且必须链接回 WHERE 子句中的父查询。

但是，您不能按照您在问题中尝试的方式使用 IIF 语句，即使您的子选择有效，但事实并非如此。

建议两个选择，尽管我不太清楚你想在这里实现什么目标。首先，您可能需要考虑使用 VBA 来完成此操作。例如：

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))

或者，如果 tbl1 和 tbl2 具有相同的字段，您可以执行以下操作：

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

如果您将 3<4 替换为您要检查的任何实际条件，您将只能从其中一个或另一个返回记录，或者查询，绝不能两者兼而有之。然而，我怀疑，如果您需要这样做，您的数据库可能存在设计问题 - 我可以想到许多需要这样做的可疑场景，并且很少有有效的场景，尽管我确信它们存在。

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.

回复收藏 0 原文

~没有更多了~