sql - 访问 VBA 字符串以根据匹配条件返回最小和最大日期

Question

我正在尝试使用字符串从ExpectedTaskStart指定表 ( tblLiveTasks) 中的字段 () 中获取最小日期，使用两个 ID 字段 ( CASEID) 上的匹配 ( ExpectedTaskStart) 另一个 ( intCaseID) 我要么没有返回，要么返回 0。我使用了下面概述的各种 VBA，但结果都相同；

'Dim rsLiveCaseSpecific As DAO.Recordset, Var, strSQL As String
Dim rsDates As Recordset
Dim strSQLGetMINDate As Double
Dim strSQLGetMAXDate As Date
Dim MINDate As String
Dim MAXDate As String
Dim intCaseID As Long
Dim intCaseID2 As Integer

Let intCaseID = Forms![frmHome]![FRMHOMECASEID]

'----------------------------------------------------------
'Below here needs sorting

MsgBox "Done New Tasks"

Let strSQLGetMINDate = DateValue("SELECT DMIN(CDate ([tblLiveTasks].[ExpectedTaskStart]) FROM tblLiveTasks WHERE CaseID = " & intCaseID)

Let strSQLGetMAXDate = DateValue("SELECT MAX ([tblLiveTasks].[ExpectedTaskEnd]) FROM tblLiveTasks WHERE CaseID = " & intCaseID)

MINDate = Format(strSQLGetMINDate, "#dd/mm/yyyy#")

MsgBox (MINDate)
MsgBox "Done Date Value"

MINDate = Format(#1/1/2020#, "dd/mm/yyyy")
MAXDate = Format(strSQLGetMAXDate, "dd/mm/yyyy")'

我也尝试将代码 SELECT MAX 更改为；DMIN/DMAX 但仍然没有结果。

标签： sqlvbams-access

Answer 1

试试这种简化的方法：

Dim SQLGetMINDate As Date
Dim SQLGetMAXDate As Date
Dim MINDate As String
Dim MAXDate As String
Dim intCaseID As Long

intCaseID = Forms![frmHome]![FRMHOMECASEID]
SQLGetMINDate = DMin("[ExpectedTaskStart]", "[tblLiveTasks]", "CaseID = " & intCaseID & "")
SQLGetMAXDate = DMax("[ExpectedTaskEnd]", "[tblLiveTasks]", "CaseID = " & intCaseID & "")

MINDate = Format(SQLGetMINDate, "dd/mm/yyyy")