format function not working in access 2016

[MarionD]

Hi there, I have just updated from Access2013 to Access 2016. All my Forms and Reports with the Format function in show an error.
=Format([fromdate];"dddd") just displays #name.
What to do now? If I add a new field it seems as though the format function is still there. Can anyone help me out here?

 

[theDBguy]

Hi,

Have you checked if there’s any missing reference in your project?

Just a thought...

 

[MarionD]

Thanks for the reply. Nothing seems to be missing ... I can compile with no error messages as well. Just seems to be on the controls on forms.

 

[theDBguy]

Okay, a #Name error usually means a typo or maybe the name of the control you’re formatting is misspelled.

 

[MarionD]

except it's everywhere I use the format() function. Not just on one control. I feel like I'm going bonkers!

 

[arnelgp]

try using comma in your Format function: Format([fromdate], "dddd") or rename the control other than its field name, ie, txtFromDate.

 

[MarionD]

The comma only works in VB, not in the controls on a form or report. Here the semi-colon should work. In the attached, the day should appear below the date.

 

[Frothingslosh]

The comma only works in VB, not in the controls on a form or report. Here the semi-colon should work. In the attached, the day should appear below the date.

Actually, you're wrong. All VBA functions including Format use commas, not semicolons, to separate parameters. Semicolons are used to separate lists in the UI.

 

[MajP]

Actually, you're wrong. All VBA functions including Format use commas, not semicolons, to separate parameters.

No, actually you are incorrect. It depends in the regional settings. If the "list separator" is a comma then it will only work with a comma. If the list seperator is a semi colon it only works with a semicolon. This is under Regional Settings, additional settings.

 

[isladogs]

Just to confuse matters further, I believe that semicolons are used instead of commas in various functions for certain language settings.
Judging by the screenshot, I think this is being done in German.

EDIT MajP got there a tad quicker.

 

[MarionD]

Hi! yes it is in German. It's always worked with a semi-colon on forms and reports and with a comma when used in VB in Access 2013 - its only since using (or trying to use) Access 2016 that it doesnt work. Strange that when I redo it it actually suggests the format function--

 

[Frothingslosh]

Ah, fair enough, thanks Colin.

Here's a thought: What is the actual name of the control? Do you have a field with an identical name? And does the control source begin with "=" ?

 

[MajP]

I think the issue is more confusing than just the regional settings in windows, because I think there are some settings as well.

I just tested on my machine by setting the list separator to a ; and get the exact behavior the OP describes

It's always worked with a semi-colon on forms and reports and with a comma when used in VB in Access 2013

The form expressions use the ; and vba still worked with the comma. But I have seen people post vba code with ; that works for them and I was confused how that was possible.

 

[MajP]

As stated this is a regional setting issue. If you want the semicolon back then change your regional settings,
go to Start > Control Panel > Regional and Language Options. | Windows 10 (Start >type Control Panel and press enter > Region)
Click Additional Settings.

Change List Separator, enter a comma or semicolon: ,

 

[Frothingslosh]

I found a thread with a similar problem at answers.microsoft.com. The user didn't mention semicolons, but everything else was the same. He solved it after going to a backup, relinking the tables, and redoing his changes.

https://answers.microsoft.com/en-us...me-error/e4e59c1d-106d-41e2-b922-adb741c0ed5b

If you have a split DB, maybe try a relink?

 

[Frothingslosh]

Well, I expect that will certainly make a mess of things if he changes the list separator if he has used a bunch of code.

Might be easier to just use a comma in the Format functions.

 

[The_Doc_Man]

From that syntax, I am guessing you are defining the .ControlSource property of some control on the report or form.

First, was my guess correct?

Second, where is this "[fromdate]" defined? Is it ALSO a control on the same form? Is it in an underlying recordset? Is it a member of the implied or explicit recordset defined in .RecordSource?

The #Name error specifically is saying that when attempting to find that entity, something could not be found that was needed for the computation. I.e. a name search failed. There are only two options here. Either Format is not defined - implying a reference error; or [fromdate] is not defined - implying either a spelling or visibility error. The constant "dddd" is there, but it is a legit input to Format if you wanted to get the day of the week spelled out.

I am ruling out a syntax error in the use of colons or semicolons because, as has been mentioned, that is dependent on language settings and other regional settings. Plus it is easy enough to test.

If you wanted to debug this, if you have an OnCurrent event routine, you could set a breakpoint there and open the Immediate window. Then issue the command

Debug.Print [fromdate]

If it is not visible in that context, you have your answer. If it is, then try

Debug.Print format([fromdate];"dddd")

See what answer comes back there. You can also try the "," variant for snorts & giggles to see if it comes back with a different answer.

 

[MarionD]

Thanks for the answers - unfortunately the problem remains.
I have compacted and repaired both Backend and front end.
Reconnected the tables,
no references missing,
decompiled and recompiled,
added new unbound fields which show the same #name. The name of the unbound control is txtday and the name of the field I am using is fromdate. The format trying to show the day of the date [fromdate]
Now I am really stumped.

The = is there.....

 

[MarionD]

Thanks again,

I think I have ruled out reference issues. Also the field [fromdate] is definitely defined in the underlying tsble.
What confuses me is that I have not changed the accdb at all from running on my old laptop with access 2013. I only transferred it to my new laptop with access 2016. If there was a spelling error, or a field not defined then it would not work on the 2013 either. This says it must be an access 2016 problem? But apart from the references and the regional settings, I dont know what else to check.

 

[Frothingslosh]

Out of curiosity, have you tried just replacing the semicolon in the Format functions with commas and not changing anything else?