You shoud save first the value of your dlookup, then compare this value later:
Dim varReturn as Variant
varValue=Dlookup(....)
If Not IsNull(varValue) Then
....
I'm sorry that the above suggestion doesn't work. I list the script for your inspection.
Private Sub chkR1_1_Click()
Dim dDtTeach1, dDtTeach2, dTeach1, dTeach2 As Date
Dim sSelectDt1, sSelectDt2, sProgram As String
Dim iRotation, iSession, iSCnt As Integer
Dim iSession1, iSession2 As Integer
Dim bFound1, bFound2 As Boolean
Dim rsSelectDt1, rsSelectDt2 As Recordset
Dim vMyVar1, vMyVar2 As Variant
'-------------------------
' Rotation 1 & Session 1
'-------------------------
iRotation = 1
iSession = 1
sProgram = "FPA"
bFound1 = False
Stop
' get the date of selected session from tblTeachTT_FPA
dDtTeach1 = DLookup("[DtTeach]", "tblTeachTT_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation & " AND [Session] = " & iSession)
' dTeach1 = Format(dDtTeach1, "dd\-mmm\-yyyy")
' Debug.Print "dDtTeach1 format is "; dDtTeach1
'---------------------------------------------------------------------------------
'
' Get the selected DATE's all records from tblTeachTT_FPA in a specified year
'
'---------------------------------------------------------------------------------
sSelectDt1 = "SELECT * FROM tblTeachTT_FPA WHERE DtTeach = #" & dDtTeach1 & "# AND AcademicYr = " & txtApptYr.Value
dTeach1 = dDtTeach1
' Debug.Print "dTeach1 format is "; dTeach1
Debug.Print "SQL sSelectDt1 is "; sSelectDt1
Set db = CurrentDb
Set rsSelectDt1 = db.OpenRecordset(sSelectDt1, dbOpenDynaset, dbSeeChanges) ' build a recordset with selected date
rsSelectDt1.MoveLast
Debug.Print "No. of recrods with the same date: "; rsSelectDt1.RecordCount
'https://msdn.microsoft.com/en-us/library/ee440526(v=office.12).aspx for change the default index of the first element from 0 to 1
rsSelectDt1.MoveFirst
'--------------------------------------------------------------
'
'
'
'--------------------------------------------------------------
Do Until rsSelectDt1.EOF
If rsSelectDt1!Session <> iSession Then ' ignore the frmTAssign_FPA form chkbox's session
Debug.Print "Session is "; rsSelectDt1!Session
iSession1 = rsSelectDt1!Session '
End If
rsSelectDt1.MoveNext
Loop
Debug.Print "DLookup is "; DLookup(iSession1, "tblTAssign_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation)
vMyVar1 = DLookup(iSession1, "tblTAssign_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation)
If Not IsNull(vMyVar1) Then
'If (Not IsNull(DLookup(iSession1, "tblTAssign_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation))) Then
dTeach1 = dDtTeach1
bFound1 = True
Debug.Print "Is Found1 : "; bFound1
Me!chkR1_1.Value = False
End If
Set rsSelectDt1 = Nothing
Set db = Nothing
'-------------------------
'
' Rotation 1 & Session 2
'
'-------------------------
iRotation = 1
iSession = 2
bFound2 = False
Stop
' get the date of selected session from tblTeachTT_FPA
dDtTeach2 = DLookup("[DtTeach]", "tblTeachTT_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation & " AND [Session] = " & iSession)
dTeach2 = Format(dDtTeach1, "dd\-mmm\-yyyy")
' Debug.Print "dDtTeach2 format is "; dDtTeach2
'---------------------------------------------------------------------------------
'
' Get the selected DATE's all records from tblTeachTT_FPA in a specified year
'
'---------------------------------------------------------------------------------
sSelectDt2 = "SELECT * FROM tblTeachTT_FPA WHERE DtTeach = #" & dDtTeach2 & "# AND AcademicYr = " & txtApptYr.Value
dTeach2 = dDtTeach2
'Debug.Print "dTeach2 format is "; dTeach2
'Debug.Print "SQL sSelectDt2 is "; sSelectDt2
Set db = CurrentDb
Set rsSelectDt2 = db.OpenRecordset(sSelectDt2, dbOpenDynaset, dbSeeChanges) ' build a recordset with selected date
rsSelectDt2.MoveLast
Debug.Print "No. of recrods with the same date: "; rsSelectDt2.RecordCount
'https://msdn.microsoft.com/en-us/library/ee440526(v=office.12).aspx for change the default index of the first element from 0 to 1
rsSelectDt2.MoveFirst
'--------------------------------------------------------------
'
'
'
'--------------------------------------------------------------
Do Until rsSelectDt2.EOF
If rsSelectDt2!Session <> iSession Then ' ignore the frmTAssign_FPA form chkbox's session
Debug.Print "iSession2 is "; rsSelectDt2!Session
iSession2 = rsSelectDt2!Session 'Recordset.Fields("ColumnName") OR Recordset!ColumnName
End If
rsSelectDt2.MoveNext
Loop
Debug.Print "iSession2 is "; iSession2
Debug.Print "DLookup is "; DLookup(iSession2, "tblTAssign_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation)
vMyVar2 = DLookup(iSession2, "tblTAssign_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation)
If Not IsNull(vMyVar2) Then
'If (Not IsNull(DLookup(iSession2, "tblTAssign_FPA", "[AcademicYr] = " & txtApptYr.Value & " AND [Rotation] = " & iRotation))) Then
dTeach2 = dDtTeach2
bFound2 = True
Debug.Print "Is Found2 : "; bFound2
Me!chkR1_1.Value = False
End If
Set rsSelectDt2 = Nothing
Set db = Nothing
'-----------------------------------------
'
' DISPLAY THE ERROR MSG IF ANY
'
'-----------------------------------------
If (bFound1 And bFound2) = True Then
MsgBox "Date Crashes on " & sProgram & vbCrLf & " " & vbCrLf & Format(dTeach1, "dd mmm yyyy") & vbCrLf & Format(dTeach2, "dd mmm yyyy"), VbMsgBoxStyle.vbCritical
Else
If (bFound1 = True) And (bFound2 = False) Then
MsgBox "Date Crashes on " & sProgram & vbCrLf & " " & vbCrLf & Format(dTeach1, "dd mmm yyyy"), VbMsgBoxStyle.vbCritical
ElseIf (bFound1 = False) And (bFound2 = True) Then
MsgBox "Date Crashes on " & sProgram & vbCrLf & " " & vbCrLf & Format(dTeach2, "dd mmm yyyy"), VbMsgBoxStyle.vbCritical
End If
End If
Set rsSelectDt2 = Nothing
Set db = Nothing
End Sub
THE FOLLOWING INFORMATION IS FROM IMMEDIATE WINDOWS
SQL sSelectDt1 is SELECT * FROM tblTeachTT_FPA WHERE DtTeach = #11/10/2016# AND AcademicYr = 2016
No. of recrods with the same date: 2
Session is 25
DLookup is 25
Is Found1 : True
No. of recrods with the same date: 2
iSession2 is 5
iSession2 is 5
DLookup is 5
Is Found2 : True
