C
Curtis
I am using Microsoft Access 2003. I have a report {rptPreliminary Reports
Still Opened} that when there are records, the report displays as normal.
However, my issue is when there are no records, I get the msg: “Run-time
error 2427. You entered an expression that has no value.†As I run the
debug, the following is displayed. If there are no records, I want to bypass
this error with a message “There are no Preliminary Reports that are
outstanding. Have a Pleasant Day!†and be returned back to the
{frmSwitchboard}. I was wondering if someone could provide a suggestion in
the coding. I have attached the SQL Select Query {qryPreliminary Reports
Still Opened} below that is the source for my report.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Date - [DateOfExam] > [PRptDays]) Then
[PRptDays].ForeColor = vbRed
Else
[PRptDays].ForeColor = vbBlack
End If
End Sub
SELECT [tblMFS Master Data List].Name, IIf([PPE]=True,"PrePlacement
Exams",IIf([PPEOA]=True,"Overseas
Exams",IIf([DeploymentExam]=True,"Deployment
Exams",IIf([PSExam]=True,"Periodic Surveillance Exams","0")))) AS TypeOfExam,
IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",IIf([PSWSPJobTitle]<>"NA",[PSWSPJobTitle],IIf([PPEJobTitle]<>"NA",[PPEJobTitle],IIf([OEJobTitle]<>"NA",[OEJobTitle],IIf([DeployJobTitle]<>"NA",[DeployJobTitle],"Others"))))))
AS TypeOfJobTitle, [tblMFS Master Data List].DateOfExam, [tblMFS Master Data
List].PRpt, [tblMFS Master Data List].PRptDays, [tblMFS Master Data
List].PRCMT1, IIf(IsNull([PRMedicalStaff])=0,[PRMedicalStaff],"Left Blank")
AS PRMedicalStaff1, [tblMFS Master Data List].PRDate, [tblMFS Master Data
List].FRpt, [tblMFS Master Data List].FRComment1, [tblMFS Master Data
List].FRMedicalStaff, [tblMFS Master Data List].FRDate
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PRpt)=Yes))
ORDER BY [tblMFS Master Data List].Name;
Still Opened} that when there are records, the report displays as normal.
However, my issue is when there are no records, I get the msg: “Run-time
error 2427. You entered an expression that has no value.†As I run the
debug, the following is displayed. If there are no records, I want to bypass
this error with a message “There are no Preliminary Reports that are
outstanding. Have a Pleasant Day!†and be returned back to the
{frmSwitchboard}. I was wondering if someone could provide a suggestion in
the coding. I have attached the SQL Select Query {qryPreliminary Reports
Still Opened} below that is the source for my report.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (Date - [DateOfExam] > [PRptDays]) Then
[PRptDays].ForeColor = vbRed
Else
[PRptDays].ForeColor = vbBlack
End If
End Sub
SELECT [tblMFS Master Data List].Name, IIf([PPE]=True,"PrePlacement
Exams",IIf([PPEOA]=True,"Overseas
Exams",IIf([DeploymentExam]=True,"Deployment
Exams",IIf([PSExam]=True,"Periodic Surveillance Exams","0")))) AS TypeOfExam,
IIf([PEPolice]=True,"Police",IIf([PEDriver]=True,"Drivers",IIf([PSWSPJobTitle]<>"NA",[PSWSPJobTitle],IIf([PPEJobTitle]<>"NA",[PPEJobTitle],IIf([OEJobTitle]<>"NA",[OEJobTitle],IIf([DeployJobTitle]<>"NA",[DeployJobTitle],"Others"))))))
AS TypeOfJobTitle, [tblMFS Master Data List].DateOfExam, [tblMFS Master Data
List].PRpt, [tblMFS Master Data List].PRptDays, [tblMFS Master Data
List].PRCMT1, IIf(IsNull([PRMedicalStaff])=0,[PRMedicalStaff],"Left Blank")
AS PRMedicalStaff1, [tblMFS Master Data List].PRDate, [tblMFS Master Data
List].FRpt, [tblMFS Master Data List].FRComment1, [tblMFS Master Data
List].FRMedicalStaff, [tblMFS Master Data List].FRDate
FROM [tblMFS Master Data List]
WHERE ((([tblMFS Master Data List].PRpt)=Yes))
ORDER BY [tblMFS Master Data List].Name;