Run-time 2427

[ErinL]

Hello everyone -

I have the following code in the OnOpen event of a subreport:

Private Sub Report_Open(Cancel As Integer)
If Me.[Period] < Me.[CurrentPeriod] Then
Me.[CurrentFiscalActualSales].Visible = True
Else
Me.[CurrentFiscalActualSales].Visible = False
End If
End Sub

When I open the report, I receive an error that says:

Run-time error '2427': You entered an expression that has no value.

When I click Debug it highlights the first line of the If statement.

Both [Period] and [CurrentPeriod] have values.

I am working in Access 2007 but the database is in 2003 format and Visual Basic is version 6.5.

Can anyone please tell me what I am doing wrong?

Thank you in advance.

 

[pbaldy]

Data is not yet available in the open event. Try the load event, or better yet the format event of the section containing that control.

 

[ErinL]

Thank you for the quick response!

I moved it to the format event and it worked perfectly.

However, it raised another question...

The field is no longer visible but the value is still included in a text box I have to total the numbers in the footer.

Is there a way of removing it from this calculation on the report or does that have to be done in the query?

 

[pbaldy]

You can create a field in the query or do this type of thing in the footer:

=Sum(IIf([Period] < [CurrentPeriod], CurrentFiscalActualSales, 0))

 

[ErinL]

I put that expression in the query and it was perfect!

Thank you again for your help!

 

[pbaldy]

Happy to help!