Avoid These Field Names

Hi all..

Is there a list/site that suggests which field names to avoid when creating a database that will be accessed via SQL statements?

I can create a field with a name of Date in an MSAccess database and it works fine if I stay within MSAccess. However if I'm using VB and SQL Statements I get errors whenever I use the SQL INSERT statement...the syntax error goes away once I rename the field from Date to DateEnd.

I know I can add some unique characters to all my field names to avoid these "reserved words", but is there a list that contains the field names to avoid?

Many Thanks

Brent

Field names like Date & Type should be avoided, however in your SQL statement enclose the field name in square brackets and the statement will work ok.

UPDATE tblMyData Set [Date] = #12/30/2000# works in Access.

The same goes for field names with more than one word eg

[My Field Name], although your better off avoiding these completely.

Thanks..

Is it acceptable to use the [Square] brackets for all field names such as

Insert Into Tbl_Position [Date, PositionDescription, CompanyID] Values etc....keeping in in mind data-types

Thanks

Brent

I think you've misunderstood me.

Try the following;

INSERT INTO tblMyData ([Date], [Type], RecId, [Another Field]) VALUES ( .....)

Many thanks...

I'll give it a try..

Brent

While the suggestions given are valid you are going to give yourself a HUGE headache down the road using that form of notation. Think about 3 months from now when you have to make a change. You want to add a new procedure and you are assigning [EmpId] to an integer but it gives you unpredictable results and you can't figure out why. After some rooting around you find out the definition of EmpId is a double, or a string or some other incompatible type!

Take a look at the Hungarian Notation used by MS <A HREF="http://msdn.microsoft.com/library/partbook/win98dh/variablenameshungariannotation.htm" target="_new">http://msdn.microsoft.com/library/partbook/win98dh/variablenameshungariannotation.htm</A>. Every field should have a prefix denoting its type, it should not include spaces. In addition to that Forms (frm), Tables (tbl), Queries (qry), Macros (mac), etc should have a prefix as well. It will make your (or your successor's) life that much easier!

While this is free advice and should be taken for what it cost you also remember to never look a gift horse in the mouth. I have had extensive experience with this and it will save your butt!

Take a read over the web site. You can also do a search on <A HREF="http://www.google.com" target="_new">http://www.google.com</A> for <font color=blue>Hungarian Notation</font color=blue> and you will find many disertations on it.

Cheers and Good Luck!/images/icons/smile.gif


Quote of the moment....
"Ability has nothing to do with opportunity." - Napoleon Bonaparte

I agree that a naming convention denoting type is vital for easy maintenance of code. However, I dislike Hungarian notation. I'll use it for objects (buttons, forms, structures etc) but for simple data types (integer, long, string), I prefer to use the type suffix instead (%,&,$). This shows the type just as well as a prefix and prevents the possibility of an incorrect prefix (true story: I was trying to debug someone elses code and getting nowhere until I realized that they had declared dwXXXX as an integer rather than a long)

"I have a plan so cunning you could put a tail on it and call it a weasel!" - Edmund Blackadder

It looks like you have been doing Basic for a while now. I recall using those conventions over 15 years ago! I prefer the more textual prefixes however the main point is that a convention is used to prevent chaos and unmanageable code.

I further like the Hungarian Notation because it is a widely known and accepted notation which means it is a simple matter to pick-up code written by someone else and immediately understand what is going on. Using an old(er) or little known convention, or worse a personal convention which really isn't a convention at all, only opens the door for confusion to subsequent readers / maintainers of that code.

Bottom line....use a convention, one one that is widely accepted and used, and enjoy the long term benefits, perhaps at the short term pain.

Cheers.

Quote of the moment....
"Ability has nothing to do with opportunity." - Napoleon Bonaparte

At the risk of inviting many disapproving comments......And by the way, I have over 30 years programming experience, with many successful commercial VB products:

For Database fields, some sort of notation is very useful.

For VB variables, I rarely use ANY notation.

If you name the variable intelligently, the names of most variables makes their type obvious, such as CustomerName (String), PaymentAmount (Currency), or DatePaid (Date). Any notation would be redundant.

Notation is only used for the rare variable that is not obvious.

Making it strCustomerName adds nothing but making it more difficult (admittedly only VERY slightly) to read at a quick glance.

That's my own opinion. By the way, I've been told that my code is quite easy to maintain by other programmers.


Helmar B. Herman, VP ProtoProducts

The point is, you do have a system. And if it works for you, great.

"I have a plan so cunning you could put a tail on it and call it a weasel!" - Edmund Blackadder