2007 syntax error

[Guest]

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

 

[Allen Browne]

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

 

[Guest]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Pete]

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,
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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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

..................

 

[Allen Browne]

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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,
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.

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

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?

 

[Guest]

Thanks Allen
These, and another I found with a Google search, will help a great deal.
Best wishes, John

 

[Guest]

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

 

[Allen Browne]

These words are case-insensitive.
DATE is as bad as date or Date.