#Name? in report textbox (2000(sr3))

Topic

New Reply

I seem to have a jinx with this one. I have a report bound to a query. The report is opened via a form that is used as a date picker. Dates are fed to parameters in the query via textboxes on the form: [Forms]![frmISAPDates]![txtStartDate] & [txtEnddate]

I have many reports that have a textbox in the report header that lists the date range: “The reports includes records from ” & [Forms]![frmISAPDates]![txtStartDate] & “and ” & [Forms]![frmISAPDates]![txtEndDate] . All of them work fine and all are referring to the same date picker. The new one I was working on tonight, for which I simply copied the textbox from an existing report gives the #Name? error. I’m sure its something silly, but it’s making me crazy! Any ideas

Reply | Quote

Replies

Are you referring to the correct form?

Reply | Quote

Have you tried deleting “The reports includes records from ” & [Forms]![frmISAPDates]![txtStartDate] & “and ” & [Forms]![frmISAPDates]![txtEndDate]

and rebuilding the expression with the expression builder?

Reply | Quote

In addition to the useful suggestions already given, make sure that the name of the text box containing the expression doesn’t equal the name of one of the fields in the record source of the report.

Reply | Quote

I followed John’s advice and used the expression builder, which also automatically solves and answer’s Pat’s advice. I added a new textbox which is Text73 assigned by Access so one assumes there are no naming conflicts?

Some other possibly useful facts I didn’t mention: The query is a crosstab, the report was built with the wizard. I have to take this report into the client today, so I will see if it will work properly on their Access 2007 machines or at least the more sophisticated built in helps will point at an error.

This is particularly frustrating since I have done this at least 10 times with this database and this form and these textboxes. All my reports print from the same form. It reminds me of that old joke about Microsoft with the fleet of beautiful jets which all function wonderfully well, but every once in a while one of them explodes for no particular reason.

Reply | Quote

Does an expression of the form

=[Forms]![frmISAPDates]![txtStartDate]

work correctly, or does that return #Name too?

Reply | Quote

That returns #Name as well. I am working on stripping down a copy to post, but it will take some time as the database is rather sprawling in nature and there is confidential data to render non-confidential.

Reply | Quote

Finally I have had time to post a copy. If you open the form and type in a start date in the first text box and and end in the second (between Nov 1 and Dec 15 2007) the report will open with the problem #Name. Field names have been changed from my original post, but the problem still persists.

Reply | Quote

Apparently including the lookup table in the query causes the problem, although I don’t know why.
In the attached version, I created a version of the query without the lookup table. I created a crude report based on this query and simply copied the labels from the page header of your report into the page header of the new one, to replace the ones generated by the wizard. I made no attempt to recreate the entire report.

Reply | Quote

Thanks Hans. The lookup table was definitely the problem and I wouldn’t have come up with that solution, although it is perfectly obvious. Too close to the problem. There was clearly some corruption in the report itself as I had to reconstruct it to get the text box to work.

Reply | Quote

Just a followup to this thread in case anyone else ends up in this situation.

I encountered the same problem as I had before with a different report referring to a different textbox from an open form. I’m in the process of revising a number of reports (about 20) and their underlying queries and need the textbox to display the results of some filtering that didn’t used to exist. (The report can display all records or a subset from one of three separate locations and I need it to indicate which records are being displayed). Many of the reports are working just fine, but I encountered one that had the identical problem in this thread. Rather than start fiddling with queries that work fine or rebuilding reports that are complicated, I decided to try and set the controlsource of an unbound textbox in the report’s open event. The report couldn’t see the textbox and couldn’t fill the controlsource. On an impulse, I decided to see if it could call a public function from a separate module to get the value. The function had no problem reading the value of the textbox on the open form and feeding it to the controlsource.

The report’s Open Event looks like this:

Private Sub Report_Open(Cancel As Integer)
Dim strSite As String
strSite = SiteName
Reports!rptEmploymentStats!txtSite.ControlSource = strSite
End Sub

The function SiteName looks like this:

Public Function SiteName() As String
Dim strSite As String
strSite = [Forms]![frmISAPDates]![txtSiteCode]
If strSite = “*” Then
SiteName = “= ‘ This Report Counts Clients from All Sites.’ ”
Else
SiteName = “= ‘ This Report Counts Clients from ” & strSite & ” Only.’ ”
End If
End Function

Works a charm. The only tricky part at all was dealing with the quotes since it needs to provide a string in exactly the format that the textbox’s controlsource requires.

Reply | Quote