Control Source syntax error after upgrading to 2013

[scratch]

Hi. I'm accustomed to using programmed fields in Access 2010 but after changing to 2013 my expressions result in a syntax error. For instance, using this expression as my control source in the property sheet:

[Field1] & " "

... where Field1 is a field in my table pops up a dialog box warning me of a syntax error, followed by a second pop-up with the message "Microsoft Access can't find the field '[Field1] & " "'' referred to in your expression.

Ideas? Maybe I need to have a library enabled or something. I'm about ready to delete 2013 and revert back to 2010.

Thanks!
Chris

[NeoPa]

Surely, even in earlier versions, it would have been :

=[Field1] & " "

Would that still work in 2013?

[scratch]

Yes, but the problem only began occurring once I upgraded.
Suggestions to avoid this error?

[zmbd]

There is one and only one possible suggestion to avoid the error... use the proper syntax as NeoPa has shown.

There are a lot of "features" from one version to another that were undocumented shortcuts, you may have stumbled on one, and the reason it was undocumented was because it was either unintentional or provided as a developer method without warrenty that the feature would be available at any future point in time.

Honestly, I don't think I've either tried to use the format you've shown... or it didn't work for me to begin with.

[EDIT]
Just tried this using my test database.
ACC2010(32Bit-Enterprise-SP2)
Both for bound and unbound form
using [PK] & " " in the control source returned a #NAME? in the control in all cases.

[scratch]

Thank you both for your assistance.

I need to clarify: I've tried with both the leading equals and without. Trying to combine terms in an expression seems to create the error.

In the property sheet under control source when I enter Fields1 or [Field1] it displays correctly. However, when I enter =[Field1] the text box displays #Error.

I'm stumped. I appreciate any help.

[NeoPa]

Scratch:
I need to clarify:

I agree. Previously we were responding to something you weren't intending to say, it would seem.

We'd love to help you if we can, but nothing is obvious from the information in here so far. So, we get some more information.

Now, if you've tried the version I've suggested and it didn't work then I'd ask you to try out using :

Field1

When tested, please report the results as clearly and accurately as possible.

[jforbes]

I've run into some strange errors when manipulating the Controls source when the Control name is the same as the Database Field name. Mostly with Reports. Access gets confused on what object is referenced when it starts evaluating things.

This is the way I've always worked around it:

1. Change the Name and Source to the Database Field name (Field1)
2. Change the Control's name to something like (txtField1)
3. Change Control's Source to what you want to see displayed ([Field1] & " ")

[zmbd]

Everything you wanted to know about #Error and were afraid to ask

#error, #Name, etc.. are very specific and we need to have the correct error messages and context to help. (^_^)

[scratch]

@NeoPa - Entering the single field as you typed (and with or without brackets) displays the correct data.

Also entering a literal string expression works (e.g. ="A" & " " & "B" yields a result of "A B".) However, entering =[Field1] & " " displays #Error.

FWIW the field is named City, not Field1. I'm using that as an example.

Thank you for your continued help.

[BikeToWork]

Are you sure the [City] field is there in the form's underlying recordsource? Is the form bound or unbound?

[scratch]

@Bike: The form is bound to a recordset with City in it. When the field City is entered, the display shows the correct data.

[NeoPa]

Scratch:
FWIW the field is named City, not Field1. I'm using that as an example.

Understood. In general, it's iffy changing the question to something generic. You rarely know what will break when doing that. In this case I see no difference but for a benefit of nothing obvious, it's not worth considering that approach. As I say though, no obvious harm in this case.

Now for the strange part. I did some testing in one of my databases (2003 in this case) and saw the #Error come up a lot. To understand what I'm about to say you must understand the difference between a Field and a Control. Controls are often, mistakenly, referred to as Fields. The data they show can be from a Field but the item on the form or report is actually a Control. Examples of Controls are TextBoxes; ComboBoxes; Labels; CheckBoxes; etc. In my testing I used two TextBox controls to refer to another by its Field name and, separately, by its Control name (Control Source = "=[PrimaryID] & ' - X'" and Control Source = "=[txtPrimaryID] & ' - X'"). At this point I'd refer you back to JForbes' post #7 where he describes the difference.

I first tried with a TextBox which had an Input Mask set and the .Value of [PrimaryID] was Null. In this case the value when using the Control reference was #Error. I used the same reference on another TextBox which had no Input Mask set and the value was fine (" - X"). I tried the same on a record where the .Value of [PrimaryID] was "ON00203". In this case both were fine and showed the expected result (ON00203 - X). So far so good - but not much good if it doesn't handle Null values.

Then I tried with the Field reference instead. This time the Control with the Input Mask would show #Error in some circumstances but not others. The other Control always showed the desired result, whether on a record with Null or not.

I suggest you take the Control you're working with and check other properties of the Control (Especially Input Mask and Format) to ensure any data shown would be valid. Then ensure your reference is to the Field [City] and not the Control. To do this you will need to make sure the Control has a different name from the Field.

Let us know how you get on.