• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

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],"ww yyyy")

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.

 Design View
 Report View with NAME? next to Schedule Date
 Report with new text box added, exact same control source in both fields
 Report View with both text boxes working. Control source has not changed
0
CJSilver
Asked:
CJSilver
  • 6
  • 6
  • 5
  • +2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try w/o $ ....

="Week " & Format([ScheduledDate],"ww yyyy")
0
 
hnasrCommented:
#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.
0
 
Jeffrey CoachmanCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
CJSilverAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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")
0
 
hnasrCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
CJSilverAuthor Commented:
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],"ww yyyy")
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],"ww yyyy")
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],"ww yyyy")
But again, it worked immediately after the change, but after saving and reloading Access I am back to getting #NAME? again.
0
 
hnasrCommented:
Upload a sample database showing the problem.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Any chance you can upload the db, explaining exactly how to reproduce the issue ?

mx
0
 
CJSilverAuthor Commented:
Unfortunately the database is a SQL database that is 9 GB is size, also it is proprietary information.
0
 
hnasrCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
The weird part is ... works until you reboot or reload ...

mx
0
 
BobOxfordCommented:
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
0
 
CJSilverAuthor Commented:
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.
0
 
hnasrCommented:
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.
0
 
CJSilverAuthor Commented:
As i assumed, just creating  a table and report does not duplicate the problem. I have made 1 table with a section of the same data and a report with the exact same control source in a text box and the problem does not show up. I see no logic to this issue.

I created a work around. I added an expression to my query
Week_Date: DateAdd("d",(2-Weekday(DateValue(Min([ScheduledDate])))),Min([ScheduledDate]))

I am using this field now instead of formatting a field in the report.

Unless someone has an epiphany I am going to close this question and call it a day.

Thank you all for your input.
0
 
hnasrCommented:
" 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.
0
 
CJSilverAuthor Commented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now