 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 20072007 syntax error
Just moved from Access 2002 to 2007 and finding some errors coming up without having changed anything. I am not a coder but doing DB for a charity. A field in a form has a drop down list to choose from. The options are from a table of parameters. I orginally did this with a graphic Query from the Form field's properties. Now 2007 flagged the syntax as in error despite the fact I didn't originally put in code but used the Query design. Here's the syntax flagged as error - SELECT Parameters.Parameter FROM [Parameters] WHERE ((Parameters.LookupType)="Payees") ORDER BY Parameters.Parameter; Any ideas on how I should change this? The [...] symbol in properties only gives an error. If it's fixed will it give the Query design? Thanks John PARAMETERS is a reserved word in JET, and PARAMETER is a reserved word in Access. You could try placing square brackets around these words, and see if Access 2007 is able to understand them correctly: SELECT [Parameters].[Parameter] FROM [Parameters] WHERE ([Parameters].LookupType = "Payees") ORDER BY [Parameters].[Parameter]; In general, it's best to avoid using reserved words as field names. Here's a list of the names can can cause you problems: http://allenbrowne.com/AppIssueBadWord.html  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Just moved from Access 2002 to 2007 and finding some errors coming up > without [quoted text clipped - 14 lines] > Thanks > John Thanks. Looks like we have lots of reserved words in use. Is there a utility, within Access or a third party program, which could globally change names thoughout a db including tables and fields, queries and conditions within queries, forms and conditions with it's fields, and macro conditions. We could sue use something like that. John > PARAMETERS is a reserved word in JET, and PARAMETER is a reserved word in > Access. [quoted text clipped - 27 lines] > > Thanks > > John If you started in 2007 it should do it automatically. If it is older version see http://www.trigeminal.com/lang/1033/utility.asp?ItemID=10#10 Access 2000's Name AutoCorrect feature (a.k.a. "Name Fixup") has some real power, but it has one big flaw: converted databases do not have it turned on by default, and even if you turn it on they do not contain the updated "name maps" that this feature needs to work properly. The Access 2000 help suggests turning on Name AutoCorrect and opening/saving all objects, but this is obviously a bit tedious. Enter the TSI Name AutoCorrect Wizard, which does all of this for you. The wizard can also be used to make sure the name maps for a database are up to date (in case you had the feature turned off for some of the time). This utility requires the VB6 runtime files to run properly (msvbvm60.dll, oleaut32.dll, etc.). Also, if you have been looking for a nice small sample of an Access 2000 ComAddIn that interacts with the Access object model, the source is downloadable as well! > Thanks. Looks like we have lots of reserved words in use. Is there a > utility, [quoted text clipped - 38 lines] >> > Thanks >> > John Pete, this is a start. The db was first designed when Access came out in 97 or so, it was updated to AccessXP-2002 with many errors, now it's been updated to 2007, again with many errors appearing without changing anything. Of course, changes and additions were done over time. Would this utulity work, even partially, for me? I'm not a coder or Access expert and would need assistance in installing and using this utility. Thanks, John > If you started in 2007 it should do it automatically. If it is older > version see > http://www.trigeminal.com/lang/1033/utility.asp?ItemID=10#10 > Access 2000's Name AutoCorrect feature (a.k.a. "Name Fixup") has some real > power, but it has one big flaw: converted databases do not have it turned on > by default, and even if you turn it on they do not contain the updated "name ................. Here's a utility to help you track down where a name is used: http://allenbrowne.com/ser-73.html Here's one to help in renaming fields and tracing depencencies: http://allenbrowne.com/ser-41.html Or, there are commerical utilities available such as: http://www.speedferret.com/ And of course, the utility to run checks over your database: http://allenbrowne.com/AppIssueChecker.html SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Thanks. Looks like we have lots of reserved words in use. Is there a > utility, [quoted text clipped - 36 lines] >> > it >> > give the Query design? Thanks Allen These, and another I found with a Google search, will help a great deal. Best wishes, John > Here's a utility to help you track down where a name is used: > http://allenbrowne.com/ser-73.html [quoted text clipped - 15 lines] > > could sue use something like that. > > John Allen Is the list you've complied of reserved words case sensitive? It looks like it but confirmation would be good. For example DATE is a reserved word. I have Date as a field name in some tables. Should this be changed or left until a problem occurs :-) Thanks, John > And of course, the utility to run checks over your database: > http://allenbrowne.com/AppIssueChecker.html These words are case-insensitive. DATE is as bad as date or Date. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Allen > Is the list you've complied of reserved words case sensitive? It looks [quoted text clipped - 6 lines] >> And of course, the utility to run checks over your database: >> http://allenbrowne.com/AppIssueChecker.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.