VBA Error in Access Report (1 Viewer)

Hello all, I am receiving the error "You entered an expression that has no value" within an Access report. The report is grouped on a field and I have the below code in the Group Header On Format property:


If FormatCount = 1 Then

' If office hours are to be posted,
If GetReportType = "Posted" Then

Dim blConfidential As Boolean
blConfidential = Nz(Me.chkConfidential, False)

' If Address is confidential for current faculty/staff member,
If blConfidential Then
' Suppress home address and phone information on report.
Me![Address Line 1].Visible = False
Me![Address Line 2].Visible = False
Me![City/State/Zip].Visible = False
Me![Home Phone].Visible = False
Else
' Otherwise display home address and phone information on report.
Me![Address Line 1].Visible = True
Me![Address Line 2].Visible = True
Me![City/State/Zip].Visible = True
Me![Home Phone].Visible = True
End If

Else ' Office hours are for internal purposes

' Display Confidential checkbox.
Me![Confidential].Visible = True

End If

End If


The code errors on the blConfidential = Nz(Me.chkConfidential, False) statement. I have changed the control name to a different name from the associated field name (Confidential). I also tried me!chkConfidential but the same error occurs. This report has been used for years so dunno why it all of the sudden started giving this error. Any thoughts?

Make sure that's the name of the checkbox and not it's associated label.

It is the correct spelling of the checkbox control. The associated label has the generic name of Label45.

Can you attach the db here? It seems like that should work.

It is attached. The name of the report is Faculty/Staff Office Hours Report.

It's because the report isn't returning any records. I changed the source to return records and the code works fine. Try adding

Cancel = True

to the report's No Data event.

By the way, that will make you want to add error handling to the code that opens the report:

http://www.baldyweb.com/ErrorTrap.htm

your report recordsource returns no records so the field does not have a value - null functions wont work because there is not a record. You need to check if any records have been returned. Usually best to do that by using say dcount of the recordsource query prior to opening the report.

Thanks for the heads up. I found out the reason there was no data in the report is because someone hard coded the criteria values into the reports query. I have it set up with a form that accepts user-supplied criteria and stores it into variables using functions. After modifying the query back to the function calls the report works. So thanks again!

Happy to help!

because someone hard coded the criteria values into the reports query

Click to expand...

hope that's not because they are sharing a front end