Run-time error 2427 - Expression has no value

[TammyT]

(See thread703-1039756 for the beginning of this discussion!)

Here's the code as I entered it:

Private Sub Report_Open(Cancel As Integer)

'ColWD27 - ColWD30 & ColDate27 - ColDate30 should be for dates at the
' beginning of the month. If not, they are from
' the previous month and we want them to be invisble

Dim intCol As Integer
For intCol = 27 To 30
If Day(Me("ColWD" & intCol)) > 3 Then
Me("ColWD" & intCol).Visible = False
Me("ColDate" & intCol).Visible = False
Me("D" & intCol).Visible = False
End If
Next

End Sub


When I try to run it I get an error that I have entered an expression that has no value (it's run-time error # 2427)

When I click on Debug - the line of code that is highlighted is

If Day(Me("ColWD" & intCol)) > 3 Then

The report Page Header contains 2 rows of text boxes to hold the date data:

ColWD (1-30) are for displaying the weekdays as Mon, Tue, etc; ColDate (1-30) are for displaying the dates in the format of 4/15, 4/16, etc. This formatting is set in the text box properties directly.

The text boxes are all located in the Page Header right now; nothing in the Details section yet.

 

[RoyVidar]

I think you will need to place the code in the on format event of the section where they reside (detail?). I don't think the controls have any values in the on open event of the report.

Roy-Vidar

 

[TammyT]

Thanks - that worked! Now all I need to do is figure out totals.

Trust me - I'm sure I'll be posting again for that!!!

 

[dhookom]

You should name your totals text boxes in a similar manner and use the On Format event of the group/report footer to set the visibility.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

That's done - I'm up to doing the totals! I've got the "grand" totals for the page footer working. I'm stuck on the date format there.

I've got the following in the text box:

="Total All Calls for " & [fldDate] & " = " & [fldCountOfTickets]

It comes up with the correct #; however, the date is displaying as 4/1/05. [fldDate] is the name of the date field in the report header; in that section I was able to set the date format to mmmm/yyyy to display it as just the month/year (it's linked to a field on a form called StartDateChoice).

However, in the text box I'm stuck on how to get it to display just the month.

Suggestions?

 

[dhookom]

="Total All Calls for " & Format([fldDate],"mmmm/yyyy") & " = " & [fldCountOfTickets]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TammyT]

That was it! I knew the format I needed, I just didn't know how to get it there; MS Help didn't help (no shock, right?!).

Remember the column headings, where we coded so that it wouldn't display the text boxes for dates from a previous month? Is there also something I can use to have it ignore Saturdays & Sundays?

Thanks!

+Tammy