Solved Creating a tracking notes field in database. (1 Viewer)

strive4peace

AWF VIP
Local time
Today, 08:32
Joined
Apr 3, 2020
Messages
918
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

AWF VIP
Local time
Today, 05:32
Joined
Mar 9, 2014
Messages
4,446
Or have to remember to enclose in [ ]. Name change is better.

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

isladogs

CID VIP
Local time
Today, 14:32
Joined
Jan 14, 2017
Messages
16,377
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}

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

strive4peace

AWF VIP
Local time
Today, 08:32
Joined
Apr 3, 2020
Messages
918
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

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2002
Messages
36,837
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.
 

Attachments

  • UsefulCode_20220427.zip
    123.3 KB · Views: 26

passaas3

New member
Local time
Today, 19:02
Joined
May 31, 2022
Messages
1
Space after Values is not necessary and won't make a difference.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:32
Joined
Feb 19, 2013
Messages
14,685
I did start a thread many moons ago asking for examples where reserved words caused issues - but did not get many takers



I note it is now locked
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2002
Messages
36,837
@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

Super Moderator
Staff member
Local time
Today, 14:32
Joined
Feb 19, 2013
Messages
14,685
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

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2002
Messages
36,837
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

Super Moderator
Staff member
Local time
Today, 14:32
Joined
Feb 19, 2013
Messages
14,685
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

AWF VIP
Local time
Today, 05:32
Joined
Mar 9, 2014
Messages
4,446
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.
 

Users who are viewing this thread

Top Bottom