Access VBA string to return a Min & Max date based on matching criteria

user_8720451 0
Viewed 228

I am trying to use a string to get a minimum date from a field (ExpectedTaskStart) in a specified table (tblLiveTasks) using a match on two ID fields (CASEID) from (ExpectedTaskStart) the other (intCaseID) I either get no return or a return of 0. I have used various VBA outlined below but all with same outcome;

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

I have also tried changing the code SELECT MAX to; DMIN/DMAX but still get no results.

ms-access vba sql
2 Answers

Try this reduced approach:

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

Consider a stored query approach to retrieve values with DLookUp without any concatenation of SQL and VBA. Below requires form, frmHome, to be opened:

SQL

SELECT MIN([ExpectedTaskStart]) AS MinDate
     , MAX([ExpectedTaskEnd]) AS MaxDate
FROM tblLiveTasks
WHERE CaseID = Forms![frmHome]![FRMHOMECASEID]

VBA

...

MINDate = Format(DLookUp("MinDate", "mySavedQuery"), "dd/mm/yyyy")
MAXDate = Format(DLookUp("MaxDate", "mySavedQuery"), "dd/mm/yyyy")
user_14224510
Related