473,325 Members | 2,872 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Control Source syntax error after upgrading to 2013

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
Sep 6 '14 #1
11 1739
NeoPa
32,556 Expert Mod 16PB
Surely, even in earlier versions, it would have been :
Expand|Select|Wrap|Line Numbers
  1. =[Field1] & " "
Would that still work in 2013?
Sep 6 '14 #2
Yes, but the problem only began occurring once I upgraded.
Suggestions to avoid this error?
Sep 8 '14 #3
zmbd
5,501 Expert Mod 4TB
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.
Sep 8 '14 #4
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.
Sep 8 '14 #5
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Field1
When tested, please report the results as clearly and accurately as possible.
Sep 8 '14 #6
jforbes
1,107 Expert 1GB
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] & " ")
Sep 8 '14 #7
zmbd
5,501 Expert Mod 4TB
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. (^_^)
Sep 8 '14 #8
@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.
Sep 8 '14 #9
BikeToWork
124 100+
Are you sure the [City] field is there in the form's underlying recordsource? Is the form bound or unbound?
Sep 8 '14 #10
@Bike: The form is bound to a recordset with City in it. When the field City is entered, the display shows the correct data.
Sep 8 '14 #11
NeoPa
32,556 Expert Mod 16PB
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.
Sep 8 '14 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
11
by: frr | last post by:
Hi, After upgrading to 2.4 (from 2.3), I'm getting a weird syntax error: >>> import themes Traceback (most recent call last): File "<interactive input>", line 1, in ? File "themes.py", line...
1
by: amitbadgi | last post by:
I am gettign this error while converting an asp application to asp.net , its a query syntax error, Exception Details: System.Runtime.InteropServices.COMException: Syntax error in query expression...
1
by: amitbadgi | last post by:
I am geting the following error while conv an asp to asp.net Exception Details: System.Runtime.InteropServices.COMException: Syntax error in query expression 'id =System.__ComObject'. Source...
1
by: amitbadgi | last post by:
HI i am getting the foll error while conv an asp application to asp.net Exception Details: System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Source Error: Line...
5
by: amitbadgi | last post by:
Hi guys, I am getting the following error in teh insert statement , I am converting this asp application to asp.net, here is teh error, Exception Details:...
8
by: Kevin Murphy | last post by:
Using PG 7.4.3 on Mac OS X 10.2.8, the following "insert into ... select ..." statement completed and then announced a syntax error, which seems bizarre. (Don't be confused by the fact that the...
2
by: technocraze | last post by:
Hi guys, I have encountered this error when updating the values to the MS Acess table. Error : Update on linked table failed. ODBC sql server error Timeout expired. MS Acess is my front end and...
4
by: hathan | last post by:
Hi, i am trying to add a new row into a microsoft access database and cant understand why it wont work. Can anyone please help me understand what this means? The error message says: **************...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.