如何使用带有两（2）表的2 DTPicker编码日期范围

2024-04-25 • 问答

Private Sub DTPicker3_Change()

   loadData "Select  CheckItem as 'Check Item',CheckItem as 'Check Item',ItemNo as 'Item No.',Criteria as 'Criteria',Mcname as 'Machine Name',AMstart as 'Start of Shift',AMafter as 'After 1st break',PMstart as 'Start of Shift',PMafter as 'After 1st break',CheckDate FROM tbl_Checklist where CheckDate BETWEEN '" & DTPicker2.Value & "'AND  '" & DTPicker3.Value & " '" '

 End Sub


Private Sub DTPicker2_Change()

  loadData "Select  CheckItem as 'Check Item',CheckDate FROM tbl_Checklist  where CheckDate BETWEEN '" & DTPicker2.Value & "'AND  '" & DTPicker3.Value & "'" 
End Sub

我之前的问题是关于如何仅在1个表中搜索两个dtpicker之间的日期范围

您可以使用UNION合并两个表并应用相同的WHERE子句：

SELECT CheckItem AS 'Check Item',ItemNo AS 'Item No',Criteria,CheckDate FROM tbl_checklist WHERE CheckDate BETWEEN #" & DTPicker2 & "# AND #" & DTPicker3 & "#"
UNION
SELECT CheckItem AS 'Check Item',CheckDate FROM tbl_Dross WHERE CheckDate BETWEEN #" & DTPicker2 & "# AND #" & DTPicker3 & "#"

由于列数不均，您将不得不将某些字段合并在一起或仅返回公共列。例如，如果您的a1，a2和a3字段包含OK或NOT OK，则可以使用以下内容将它们组合为一个字段：

IIF(a1 = 'OK' AND a2 = 'OK' AND a3 = 'OK','OK','NOT OK') AS AMstart

或者您可以将字段串联成一个：

SELECT a1 + '-' + a2 + '-' + a3 AS AMstart

以下是同时使用这两个示例的示例：

SELECT CheckItem AS 'Check Item',CheckDate,AMstart,AMafter,PMstart,PMafter,Mcname FROM tbl_checklist WHERE CheckDate BETWEEN #" & DTPicker2 & "# AND #" & DTPicker3 & "#"
UNION
SELECT CheckItem AS 'Check Item',a1 + '-' + a2 + '-' + a3 AS AMstart,IIF(a4 = 'OK' AND a5 = 'OK' AND a6 = 'OK','NOT OK') AS AMafter,a7 + '-' + a8 + '-' + a9 AS PMstart,IIF(a10 = 'OK' AND a11 = 'OK' AND a12 = 'OK','NOT OK') AS PMafter,Mcname FROM tbl_Dross WHERE CheckDate BETWEEN #" & DTPicker2 & "# AND #" & DTPicker3 & "#"

如何使用带有两（2）表的2 DTPicker编码日期范围

lyx123hsh 回答：如何使用带有两（2）表的2 DTPicker编码日期范围

大家都在问