Solved Creating a tracking notes field in database.

[strive4peace]

NOTE is on Allen Browne's reserved word list

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

 

[JamieRhysEdwards]

NOTE is on Allen Browne's reserved word list

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

THAT WAS IT! I changed it to ContactNote and then updated my code to reflect the new change and it's worked.... Don't make it easy sometimes do they? XD

 

[strive4peace]

glad its working, @JamieRhysEdwards ~ happy to help

I have Allen's reserved word page bookmarked because I use it a lot! It's the most complete list there is even though he hasn't updated it in a long time

 

[June7]

Or have to remember to enclose in [ ]. Name change is better.

He never will update but I doubt there have been significant changes.

 

[isladogs]

Interesting that Note isn't on the official MS list of reserved words!
However as a test I added a field called Note & created an append query. Access automatically modified it to [Note}

INSERT INTO Table1 ( N, [Note] )
SELECT 2345 AS N, "ABCD" AS Expr1;

 

[strive4peace]

Interesting that Note isn't on the official MS list of reserved words!

lots of words are left off that list ... maybe you should fix it, @isladogs -- oh I guess you can't since it's not on Docs ... there is probably an incomplete list somewhere there too -- although that list is just for SQL, not also Access

 

[Pat Hartman]

We often talk about Reserved words and bad names but don't offer examples of some of the bad things that happen when you use them. Here's a form that uses Name and Date which are the worst offenders I found, although Now probably has the same problem as date as well as two names with characters not supported by VBA (or most other programming languages either)

There is a button next to each data field. Pressing the button shows what you get for a value depending on the code you use to reference the variable.

Look at the code behind the form to see that when you add event code for the bad names like Amt$ and Amt%1. Create a table with two columns that are identical except for the bad character such as Tot$ and Tot% and see the errors you get because Access tries to name them both Tot_ as it replaces the offending characters with the underscore.

 

[strive4peace]

> Tot_
interesting to note that, @Pat Hartman

 

[passaas3]

Space after Values is not necessary and won't make a difference.

 

[Gasman]

Space after Values is not necessary and won't make a difference.

That has already been mentioned in post #15 ?

 

[CJ_London]

I did start a thread many moons ago asking for examples where reserved words caused issues - but did not get many takers

Consequences - Reserved Words

This thread is intended to provide real life examples of the potential consequences of using reserved words in field and table names. A link to reserved words can be found here...

www.access-programmers.co.uk

I note it is now locked

 

[Pat Hartman]

@CJ_London I'm not sure people notice or recognize what caused the problem. In the case of date for example, if they notice, they just switch to Now() and don't actually recognize they caused the problem. Also since novices don't do much VBA, not many will notice the problem with "Name".

 

[CJ_London]

the problem is when they do cause a problem any accompanying error message is generally misleading at best - certainly doesn't say 'reserved word, should not use'.

 

[Pat Hartman]

At least MS added warnings when you create tables to not use some of the words but people still just blow by them

 

[CJ_London]

Sure do - but it is not a complete list, Name and Date for certain, but not Now and per the OP, Union.

Just checked a few others commonly used - Order, Desc, Description, they don't get flagged

 

[June7]

I seem to remember having to rename a field because using it in query caused issue and even wrapping in brackets didn't help. Can't remember the word.