format function not working in access 2016

[theDBguy]

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.

Hi,

Just to clarify a small point, there is a difference between the name of the field and the name of the control (textbox) on the form. I think when you use an expression in the Control Source, you might have to make sure you're referring to the name of the control. If the name of the field and the control are the same (fromdate), then try changing the name of the control and then adjust the =format() expression to use the name you selected for the control.

 

[MarionD]

Thanks, I have tried that as well.
I have made a new form, using an unbound control to input a date. The name of the unbound control is "txt_fromdate" . Then I put another control called txt_day on with =format([txt_fromdate];"dddd") in the control source. When putting this expression in, format is even suggested, giving the expected input as well, - but when I input the date, the same #name appears in the control txt_day. Normally this would be fine??

 

[theDBguy]

Hi,

Not sure what to say. I just tried it and it worked. I didn't get any "suggestions" probably because I just typed the expression in the box. I'm in the U.S., so I used a comma. I'm using Access 2016, but I think it's not Office 365. Have you tried creating a new blank Access 2016 database file just to see if it will have the same issue? I am thinking maybe it has something (somehow) to do with the old file from 2013.

 

[Frothingslosh]

From what I can tell, he's tried everything suggested except trying to run it with a comma (on the off chance that it's a strange 2016 bug involving Format and regional settings).

 

[Deleted member 147267]

I'm probably not the only one here with Office 365? Would be willing to test your db, even to the point of temporarily changing my settings.

 

[MajP]

One thing interesting about changing the list separator. Assume your list separator in windows is a comma and you do this
=Format(Date(),"yyyy")
Close the DB and change the list separator in windows to a ;
When you reopen the database and look at the control it will work and automatically update to
=Format(Date();"yyyy")