VBA Error in Access Report

fishindude64

New member
Local time
Today, 14:22
Joined
Sep 14, 2018
Messages
4
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'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.
 
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!
 
because someone hard coded the criteria values into the reports query
hope that's not because they are sharing a front end
 

Users who are viewing this thread

Back
Top Bottom