时间戳

首页 > 解决方案 > 访问表单上的选项组作为查询参数

sql - 访问表单上的选项组作为查询参数

问题描述

我在 Access 2016 中工作，我有一个用户可以从中选择要运行报告的培训类型。所有训练的选项值为 1，Bloodborne 的选项值为 2，依此类推。我想获取表单的值并将其传递到查询中，以便显示所有接受过培训的员工。

我的表单是 View February Training 并且在名为 FrameAllorCurrent 的框架内包含一个选项组。我的查询包含训练字段，我可以使用以下内容进行过滤，例如“68”是否 68 是训练表中的训练 ID。

我知道我必须按照 [forms]![View February Training].[FrameAllOrCurrent].Value = 2 的方式在查询的培训字段中做一些事情，但是我该如何做到这一点，所以当它等于 2 时，它会进行培训字段“68”并返回我所有的血源训练？

SQL查询：

SELECT CompletedTrainings.RecordID
    ,CompletedTrainings.Employee
    ,CompletedTrainings.Training
    ,CompletedTrainings.CompletedDate
    ,CompletedTrainings.ExpiredDate
    ,EmployeeInformation.Employee
    ,EmployeeInformation.Active
    ,Trainings.TrainingID
    ,Trainings.[Training Name]
FROM Trainings
INNER JOIN (
    EmployeeInformation INNER JOIN CompletedTrainings 
        ON EmployeeInformation.ID = CompletedTrainings.Employee
        ) ON Trainings.TrainingID = CompletedTrainings.Training
WHERE (((EmployeeInformation.Active) LIKE "-1"))
ORDER BY Trainings.[Training Name]

标签： sqlms-access

解决方案

有几种解决方案。但一种方法是：

SELECT CompletedTrainings.RecordID
    ,CompletedTrainings.Employee
    ,CompletedTrainings.Training
    ,CompletedTrainings.CompletedDate
    ,CompletedTrainings.ExpiredDate
    ,EmployeeInformation.Employee
    ,EmployeeInformation.Active
    ,Trainings.TrainingID
    ,Trainings.[Training Name]
FROM Trainings
INNER JOIN (
    EmployeeInformation INNER JOIN CompletedTrainings 
        ON EmployeeInformation.ID = CompletedTrainings.Employee
        ) ON Trainings.TrainingID = CompletedTrainings.Training
WHERE EmployeeInformation.Active LIKE "-1"
AND [Training Name] = 
    SWITCH (
        [Forms]![View February Training].[FrameAllOrCurrent] = 1, "Whatever 1 should be",
        [Forms]![View February Training].[FrameAllOrCurrent] = 2, "68",
        [Forms]![View February Training].[FrameAllOrCurrent] = 3, "If there's a 3... etc."
        )
ORDER BY Trainings.[Training Name]

注意：我不确定您的相关字段是什么，因此将其列为[Training Name]，这也假设该列是TEXT格式的，基于您之前的引用。如果数据类型是数字，只需删除引号。

推荐阅读