Calculated field #Name? error

[Huddle]

I have several calculated fields in two unrelated forms that now read #Name?. When I saved that database a couple of days ago they worked. In the backup database they work. I even compared the backup database formulas with the original database and the formulas are exactly the same.

The only difference between the database is I added some fields to a form and made some reports.
I didn't change any field names.
I did a "compact & repair".

Any idea what could be causing this?

[ssanfu]

I have several calculated fields in two unrelated forms that now read #Name?. When I saved that database a couple of days ago they worked. In the backup database they work. I even compared the backup database formulas with the original database and the formulas are exactly the same.

The only difference between the database is I added some fields to a form and made some reports.
I didn't change any field names.
I did a "compact & repair".

Any idea what could be causing this?

Some where you have to have changed the name of a control or field.
"#Name" is an error that means that Access can't find a referenced field/control name.
Look at the calculated control and locate each part of the formula. You should find (or shouldn't find) one or more of the field/control names.

[Huddle]

OK, I have gremlins...I opened it to do as you suggested and now it is working. I didn't change a thing. That scares me.

[Huddle]

Take that back. I closed it and opened it again to double check it and now it's giving the errors again.

Three of the five calculations are unrelated. So it can't be just one field/control name.

Example:
GSA Contact is a combo box name GSA_Contact_Cmb whose Row Source is directed to a table.
The first calculated box is =[GSA_Contact_Cmb].[column](1) & " x" & [GSA_Contact_Cmb].[column](2)
The second calculated box is =[GSA_Contact_Cmb].[column](3)

On another Tab I have Last_Signature_Date which is manually entered.
The first calculated field is =IIf(IsNull([Last_Signature_Date])," ",DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Start_Date] <= #" & [Last_Signature_Date] & "# And [End_Date] >= #" & [Last_Signature_Date] & "#"))

The second calculated box is =IIf([Status]="Closed","Inactive",IIf([FSL]<>0,IIf([FSL]=4,[Last_Signature_Date]+1096,IIf([FSL]=3,[Last_Signature_Date]+1096,IIf([FSL]=2,[Last_Signature_Date]+1827,IIf([FSL]=1,[Last_Signature_Date]+1827)))),"Need FSL"))

And the last calculated box is =IIf([Next_FSA]="Inactive"," ",IIf([Next_FSA]="Need FSL"," ",IIf(IsNull([Next_FSA])," ",DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Start_Date] <= #" & [Next_FSA] & "# And [End_Date] >= #" & [Next_FSA] & "#"))))

The only commonality is they are on Tabs.

There is also a Macro for a pop up form that will not run with the Command Button but when I click outside the Tabs it pops up.

[ssanfu]

Take that back. I closed it and opened it again to double check it and now it's giving the errors again.

Three of the five calculations are unrelated. So it can't be just one field/control name.

Example:
GSA Contact is a combo box name GSA_Contact_Cmb whose Row Source is directed to a table.
The first calculated box is =[GSA_Contact_Cmb].[column](1) & " x" & [GSA_Contact_Cmb].[column](2)
The second calculated box is =[GSA_Contact_Cmb].[column](3)

On another Tab I have Last_Signature_Date which is manually entered.
The first calculated field is =IIf(IsNull([Last_Signature_Date])," ",DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Start_Date] <= #" & [Last_Signature_Date] & "# And [End_Date] >= #" & [Last_Signature_Date] & "#"))

The second calculated box is =IIf([Status]="Closed","Inactive",IIf([FSL]<>0,IIf([FSL]=4,[Last_Signature_Date]+1096,IIf([FSL]=3,[Last_Signature_Date]+1096,IIf([FSL]=2,[Last_Signature_Date]+1827,IIf([FSL]=1,[Last_Signature_Date]+1827)))),"Need FSL"))

And the last calculated box is =IIf([Next_FSA]="Inactive"," ",IIf([Next_FSA]="Need FSL"," ",IIf(IsNull([Next_FSA])," ",DLookUp("[FSA_FY]","[Lookup_Tbl_Fiscal_Year]","[Start_Date] <= #" & [Next_FSA] & "# And [End_Date] >= #" & [Next_FSA] & "#"))))

The only commonality is they are on Tabs.

There is also a Macro for a pop up form that will not run with the Command Button but when I click outside the Tabs it pops up.

Have you done a "Compact and Repair"?

[Huddle]

Yes. What I probably will end up doing is taking the backup and redo all the work since then. I just wish I knew what caused it to prevent it from happening again.

[Huddle]

Re-did the DB. It's easier the second time around. I think it had something to do with the Macro. First time I made a Macro to open the pop up form. Second time I used the wizard when creating the command button. Not sure logically why that would make a difference but it seems to work OK. We'll hold our breath.

[ssanfu]

Re-did the DB. It's easier the second time around. I think it had something to do with the Macro. First time I made a Macro to open the pop up form. Second time I used the wizard when creating the command button. Not sure logically why that would make a difference but it seems to work OK. We'll hold our breath.

I never use macros, so I wouldn't have any idea. Glad you got it working....

[ork2002]

This is happening to me too; except when I physically run the form query, I no longer get the #Name errors.

[ork2002]

So if I just copy the form in the navigation pane and paste it and rename it, then this problem goes away. It seems that when I put a subreport into my form it added some bit of code that did not get deleted when I removed the subreport. When you copy and paste the form, it removes this background code that seems to interfere with the form query and all is fixed.