Getting #NAME? in Access Report
I am getting #NAME? in a report in Access. This field is only formatting another field in the group header. The field that is giving me #NAME? has this for a control source ="Week " & Format$([ScheduledDate],"w
The ScheduledDate field shows up fine on the report. When I first created the report, all fields showed without any problem. After about two weeks I started to see #NAME? on the "format by week" field in the group header. But if I created a new text box right next to the field and copied the control source from the problem field into the new text box, then both fields worked properly. I can delete either the new field or the old "problem" field and the remaining field works fine. But once I save the report and close Access and re-open it again, then I have the same problem all over again.
I have read about a similar issue if there are similar names, but I do not see any similarities, the field name is text50
Below are screen shots.
The ScheduledDate field shows up fine on the report. When I first created the report, all fields showed without any problem. After about two weeks I started to see #NAME? on the "format by week" field in the group header. But if I created a new text box right next to the field and copied the control source from the problem field into the new text box, then both fields worked properly. I can delete either the new field or the old "problem" field and the remaining field works fine. But once I save the report and close Access and re-open it again, then I have the same problem all over again.
I have read about a similar issue if there are similar names, but I do not see any similarities, the field name is text50
Below are screen shots.
#Name?
Make sure field is bound to an existing table field. x yields #Name?
Make sure that the control source evaluates to a value. = dontKnow yields #Name
Check the control source in the immediate window and see if it evaluates to a value.
Make sure field is bound to an existing table field. x yields #Name?
Make sure that the control source evaluates to a value. = dontKnow yields #Name
Check the control source in the immediate window and see if it evaluates to a value.
General stuff.
Make sure you are running the Compact/Repair utility on a regular basis.
How old is this report?
If you drop a plain empty textbox on the form, what "Number" is it?
A from/report can have a max of about 700 controls on it over it's lifetime.
So if you are close to this limit, this may be the reason.
Make sure you are running the Compact/Repair utility on a regular basis.
How old is this report?
If you drop a plain empty textbox on the form, what "Number" is it?
A from/report can have a max of about 700 controls on it over it's lifetime.
So if you are close to this limit, this may be the reason.
ASKER
databaseMX
I tried removing the dollar sign, it worked until I closed access and restarted it again. The $ is still removed but I am getting #NAME? again.
hnasr
I am not sure what you mean. The field is not bound to a field in the table, it is just a text box that is formatting another field that is bound to a table. [ScheduleDate] s a field in a table, my text box is just formatting that field to give the week number.
You can see in my screenshot that scheduleDate is returning a value. You can also see in the last screen shot that when I duplicate the text box I do get a value returned.
boad2000
The field number is 51, the report is only about 2 months old.
I tried removing the dollar sign, it worked until I closed access and restarted it again. The $ is still removed but I am getting #NAME? again.
hnasr
I am not sure what you mean. The field is not bound to a field in the table, it is just a text box that is formatting another field that is bound to a table. [ScheduleDate] s a field in a table, my text box is just formatting that field to give the week number.
You can see in my screenshot that scheduleDate is returning a value. You can also see in the last screen shot that when I duplicate the text box I do get a value returned.
boad2000
The field number is 51, the report is only about 2 months old.
Ok ... I think I see.
"it is just a text box that is formatting another field that is bound to a table. [ScheduleDate]
Give that Text Box a Name like
txtScheduledDate
Then try
="Week " & Format$([txtScheduledDate],"ww yyyy")
"it is just a text box that is formatting another field that is bound to a table. [ScheduleDate]
Give that Text Box a Name like
txtScheduledDate
Then try
="Week " & Format$([txtScheduledDate],"ww yyyy")
Just notation:
What you have is a bound field to a table field. So this is not a problem.
But if you add formatting, then you have to add = then the expression. So you need to check that and see if it returns a value.
Example:
f1 on form bound to field d in table, which is a date type.
if we write just d inside f1 then the result displays the d values in the table.
If we write =Year(d) it displays the year of the date
If we write Year(ddd) the result is #Name?
If you type this in immediate window, and click the each line and press enter
dx=date
?format(dx,"ww yyyy") you get a an accepted result (35 2012), so you can add that in the field:
f1 = Format(d, "ww yyyy"), it should work
What you have is a bound field to a table field. So this is not a problem.
But if you add formatting, then you have to add = then the expression. So you need to check that and see if it returns a value.
Example:
f1 on form bound to field d in table, which is a date type.
if we write just d inside f1 then the result displays the d values in the table.
If we write =Year(d) it displays the year of the date
If we write Year(ddd) the result is #Name?
If you type this in immediate window, and click the each line and press enter
dx=date
?format(dx,"ww yyyy") you get a an accepted result (35 2012), so you can add that in the field:
f1 = Format(d, "ww yyyy"), it should work
In reports especially, Access get confused when the Name of a Control is the same as the Control Source (which is the default done by wizard, etc) ... when you try to reference either. So, you must be sure the Name of the Control is different than the Control Source, typically by applying a standard naming convention ... as I suggested.
mx
mx
ASKER
DatabaseMX
If I do exactly what you say, I get a parameter window opening up for TxtScheduledDate because there is no field with that name in my query.
I think you meant for me to change the [ScheduledDate] name to txtScheduledDate.
Here is what I have tried:
1) As I mentioned above, first I tried exactly what you suggested, Named my text box txtScheduledDate tehn set my text by control source to:
="Week " & Format$([txtScheduledDate]
I get a parameter window asking for for a value for txtscheduleddate.
2) I named my text box TxtWeekDate, I changed the scheduleddate name to txtscheduleddate and had the control source for my text box as
="Week " & Format$([txtScheduledDate]
But I still get #NAME? after restarting Access.
3) I named my text box TxtWeekDate, I changed the ScheduledDate name to txtscheduleddate, but I had my control source for the text box still point to the query field
="Week " & Format$([ScheduledDate],"w
But again, it worked immediately after the change, but after saving and reloading Access I am back to getting #NAME? again.
If I do exactly what you say, I get a parameter window opening up for TxtScheduledDate because there is no field with that name in my query.
I think you meant for me to change the [ScheduledDate] name to txtScheduledDate.
Here is what I have tried:
1) As I mentioned above, first I tried exactly what you suggested, Named my text box txtScheduledDate tehn set my text by control source to:
="Week " & Format$([txtScheduledDate]
I get a parameter window asking for for a value for txtscheduleddate.
2) I named my text box TxtWeekDate, I changed the scheduleddate name to txtscheduleddate and had the control source for my text box as
="Week " & Format$([txtScheduledDate]
But I still get #NAME? after restarting Access.
3) I named my text box TxtWeekDate, I changed the ScheduledDate name to txtscheduleddate, but I had my control source for the text box still point to the query field
="Week " & Format$([ScheduledDate],"w
But again, it worked immediately after the change, but after saving and reloading Access I am back to getting #NAME? again.
Upload a sample database showing the problem.
Any chance you can upload the db, explaining exactly how to reproduce the issue ?
mx
mx
ASKER
Unfortunately the database is a SQL database that is 9 GB is size, also it is proprietary information.
I don't mean the whole/actual database, just try to recreate the issue using a form and local table.
This is actually what we do if we understand the problem. You may solve the problem just by recreating a sample database.
This is actually what we do if we understand the problem. You may solve the problem just by recreating a sample database.
The weird part is ... works until you reboot or reload ...
mx
mx
Just browsing through here. You have probably tried the obvious but I don't see it mentioined here. Have you done a compact and repair? If not, I would make sure all the name references to the controls are currently correct (Sometimes when you rename a control Access will try to fix up everything else and sometimes it doesn't do what you want) and then I would do a compact & Repair...twice and see if the problem persists.
Bob
Bob
ASKER
This database is all made up of links to SQL views, there are no Access tables so there isn't much to compact. But I did run it anyway, no change. As far as the names go, I can copy the control source to a new text box and after copying, both text boxes work, the old and the new. They work until access is closed. Once I re-open Access they no longer work. Then I need to copy the control source to a new text box again.
I see no logic to this.
I am working on getting an example posted. But I don't think that just copying the data into a table is going to duplicate the problem. I hope it does, if I need to accurately duplicate all the queries and tables it will take more time then I have to dedicate to this.
But we shall see, I will either having a working example today or I will close the question, unless someone else can come up with a fix.
I see no logic to this.
I am working on getting an example posted. But I don't think that just copying the data into a table is going to duplicate the problem. I hope it does, if I need to accurately duplicate all the queries and tables it will take more time then I have to dedicate to this.
But we shall see, I will either having a working example today or I will close the question, unless someone else can come up with a fix.
Import 1 table to a local database and try to recreate the report with few fields including the one in this issue. Once done you may solve the problem. if not then upload that database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
" the problem does not show up. I see no logic to this issue."
if you cannot reproduce the issue, then it will be difficult to guess the logic behind that.
if you happy with the workaround that's fine.
We can spend extra time investigating if you upload both the newly created report and the old one linked to sql without data and removing unnecessary objects.
if you cannot reproduce the issue, then it will be difficult to guess the logic behind that.
if you happy with the workaround that's fine.
We can spend extra time investigating if you upload both the newly created report and the old one linked to sql without data and removing unnecessary objects.
ASKER
Though there were many good comments, none were able to solve my problem. I changed my report so the "formatting was done in the query instead of the report.
="Week " & Format([ScheduledDate],"ww