Is it ok to have Field names with spaces? (1 Viewer)

jonathanchye · Mar 17, 2011

I am thinking of moving into SQL Server 2008 in the future so I am wondering about Field Names. Is it ok to have spaces in them ie Date Created or is it better to use DateCreated?

I've read somewhere SQL servers do not like spaces in names but I am wondering if this would apply if I plan to upgrade to MS SQL Server 2008 R2 from MS Access 2010.

Thank you.

DCrake · Mar 17, 2011

It is recommended that you do not use spaces in field and table names as it adds to additional programming considerations. CamelCase is the preferred method.

vbaInet · Mar 17, 2011

http://www.acc-technology.com/namconv.htm

http://www.mvps.org/access/general/gen0012.htm

jonathanchye · Mar 17, 2011

vbaInet said:
http://www.acc-technology.com/namconv.htm

http://www.mvps.org/access/general/gen0012.htm

I currently follow this naming convention for object names, form names etc. I am currently using CamelCase for Field Names of tables but I am thinking of using spaces as it would be more convenient when creating controls with labels...

So for example I have a table called tblUsers

In this table I have 3 columns (Fields) called UserName, UserPassword and DepartmentID

So instead of naming them like that I was wondering if I can name them : User Name, User Password and Department ID because if I do this all the labels created will automatically reflect this.

Not a big hassle though just wondering if a space in Field names makes any difference.

vbaInet · Mar 17, 2011

Well, you will have to weigh the options. Will it be more difficult for you to simply rename the controls (once) or enclose your field names and control names in square brackets everytime you want to reference them because they contain spaces?

So no difference, just preference.

JANR · Mar 17, 2011

Why not use the Caption property of the tablefield, that is what it is there for?

Caption: Customer Name
FieldName: CustName

JR

SeanD · Mar 17, 2011

If you need a specific field name in SQL, and it has spaces, use [field name].

But try to avoid them ^^

jonathanchye · Mar 17, 2011

Ah I see... thanks for all the tips guys! I always thought caption was just a "comments" section only didn't know it would also link to created buttons etc... Would be nice to have all labels and field names properly named to avoid confusion

pkstormy · Mar 17, 2011

I would highly, HIGHLY recommend NOT using spaces (or other odd characters such as: !@#$%^&*(). If you do a lot of coding, spaces becomes a pain to deal with using brackets all the time and it doesn't pay to spend hours and hours troubleshooting syntax just because there's spaces or other odd characters in the field/table name. It may not seem like a big deal but when you're writing a lot of vba code (now or in the future), it can save you precious development/troubleshooting time.

If you need to separate wording in the name, use the _ instead of a space.

As a General rule, I follow this:
1. No spaces in any field/table names.
2. Don't start field/table names with numbers (I found this can be problematic...ex: 1SomeTableName or 2SomeFieldName or 3_SomeFieldName...etc...Numbers after are ok such as: SomeName1, SomeOtherName2...etc...otherwise again, plan on using brackets.)
3. Avoid using any 'special' characters as mentioned above.
4. Avoid long, long field names (such as field names that are 100+ characters long! - I had to work with a db once where someone named all their fields like this:
Are_you_21_years_old_or_at_least_18_years_old_starting_on_Dec_of_this_year. (imagine every freakin field name like this.)

One of my biggest pet peeves is when command buttons are named like: Command1, Command2, etc... instead of things that make sense like: cmdDelete, cmdClose, etc... For the few seconds it takes to name fields/buttons properly, it doesn't pay to name things idiotically and then spend the extra time tracing the name (when you haven't had to make code changes in the db for a year or so.)

Just remember that you (or someone else) will most likely at some point need to modify the code. You can make it easy on yourself (or someone else) and save a lot of time by simply naming things properly/easier.

Which of the following is easier to troubleshoot (this is a simple example but image a SQL statement with joins, etc.....):

strSQL = "Select FirstName, LastName From tblCustomers"

or

strSQL = "Select [First name], [Last name] From [my customer table]"

vbaInet · Mar 17, 2011

pkstormy said:
I had to work with a db once where someone named all their fields like this:
Are_you_21_years_old_or_at_least_18_years_old_starting_on_Dec_of_this_year.

No kidding!! That_must_have_been_a_bloody_nightmare_to_deal_with

pkstormy · Mar 17, 2011

vbaInet said:
No kidding!! That_must_have_been_a_bloody_nightmare_to_deal_with

I thought it was somebody playing a joke on me when they showed me the db.

vbaInet · Mar 17, 2011

I bet they never used the With block so I can only imagine how long a line of code would look like

Wiz47 · Mar 17, 2011

pkstormy said:
I would highly, HIGHLY recommend NOT using spaces (or other odd characters such as: !@#$%^&*(). If you do a lot of coding, spaces becomes a pain to deal with using brackets all the time and it doesn't pay to spend hours and hours troubleshooting syntax just because there's spaces or other odd characters in the field/table name. It may not seem like a big deal but when you're writing a lot of vba code (now or in the future), it can save you precious development/troubleshooting time.

If you need to separate wording in the name, use the _ instead of a space.

As a General rule, I follow this:
1. No spaces in any field/table names.
2. Don't start field/table names with numbers (I found this can be problematic...ex: 1SomeTableName or 2SomeFieldName or 3_SomeFieldName...etc...Numbers after are ok such as: SomeName1, SomeOtherName2...etc...otherwise again, plan on using brackets.)
3. Avoid using any 'special' characters as mentioned above.
4. Avoid long, long field names (such as field names that are 100+ characters long! - I had to work with a db once where someone named all their fields like this:
Are_you_21_years_old_or_at_least_18_years_old_starting_on_Dec_of_this_year. (imagine every freakin field name like this.)

One of my biggest pet peeves is when command buttons are named like: Command1, Command2, etc... instead of things that make sense like: cmdDelete, cmdClose, etc... For the few seconds it takes to name fields/buttons properly, it doesn't pay to name things idiotically and then spend the extra time tracing the name (when you haven't had to make code changes in the db for a year or so.)

Just remember that you (or someone else) will most likely at some point need to modify the code. You can make it easy on yourself (or someone else) and save a lot of time by simply naming things properly/easier.

Which of the following is easier to troubleshoot (this is a simple example but image a SQL statement with joins, etc.....):

strSQL = "Select FirstName, LastName From tblCustomers"

or

strSQL = "Select [First name], [Last name] From [my customer table]"

Very good points. I would also add that the names of ALL controls should also follow the name of the Source. For example a field named Lastname should have a corresponding text box control named txtLastname. That way it is much easier to keep track when you are trying to build queries and/or debugging the code.

gemma-the-husky · Mar 18, 2011

Personally, I would also not use the caption property in fields.

its so irritating looking at a query and seeing a fieldname that doesn't correspond to any field in the underlying table - bcause it's sohwing you the caption.

I also don't use spaces, as it avoids having to wrap fields in [] characters.

Simon_MT · Mar 19, 2011

I'm very naughty I do use spaces. I have always used brackets but I got annoyed with a database I had to convert from Informix with fields of simply single or double letters. So when I worked out what each one was and through a process of elimination the fields became descriptive. I would rather have fields sensibly named rather totally obscure or enigmatic names. I got the hump!

Simon

Lightwave · Mar 19, 2011

I actually name my forms queries and tables with the following format

Forms
F001descriptionofform

Queries
Q001descriptionofquery

Tables
T001descriptionoftable

Also more genearlly I actually name all my files

as

yearsuffixincrementalnumberdescription

so an excel file would be

2011xls001FirstExcelfileoftheyear

2012doc001FirstWordfileof 2012

etc , etc..

I find that I remember when I did the work better than exactly what I called the file.. So old stuff at top new stuff at bottom

I also try to store all my files by their extension all together

xls in xls directory
doc in doc directory

Then I keep them all these sub directories in one directory which makes transfer / linking easier and if I need to tell someone about a file I just need to give them the year and an incremental number and provided they know what document type it is I can generally define any file in 7 characters - (I have never produced more than 999 files in one application in a year)

Hasn't caught on at work but I really like it.

Particularly good with pdf documents with the thumnails as I usually roughly remember the date I did it and the thumbnail is enough for me to get to it quick.

I have heard of a person that has created his own shorthand computer speak. So when he types stuff in his wordprocessor. His typing concatenated codes produce full words , sentences and sometimes even paragraphs... He was a tech writer for a living.

This Douglas Engalbart demo is quite fun - go to 5 minutes and you see he has a keyboard device like a piano which allows him to make cords etc... which correspond to request for actions...

http://www.youtube.com/watch?v=JfIgzSoTMOs

Simon_MT · Mar 19, 2011

The point I was trying to make is no matter what naming convention you use it is the data contained with the tables that is really important. Lets face it; a field is a field - contents are contents.

Simon

Brianwarnock · Mar 19, 2011

A naming convention should
a - be consistent
b - help the understanding and programming of the system
c - exploit rather than confound the software.

Spaces in names do not allow c . eg When writing code type me. and with spaces intellisense cannot help you

just my 2pence worth

Brian

DCrake · Mar 19, 2011

I think the worst thing is/was what I encountered when updating a legacy system a few years ago. It was de normalised and was written by someone with no experience apart from loking over the shoulder of someone who had some semblence of knowledge.

A major part of the application was a download routine from their finance software into Access to assist the production database. The incoming and main table had fields named field1, field2, field3,....field10. Apart from fields named conventionaly. It took me about 3 or 4 days to get my head around what was going one, this was before I even wrote one line of code.

PhoenixofMT · Aug 2, 2012

I just found out the hard way that hyphens are also a no-no. :banghead:
InteliSense thinks you are trying to do subtraction and automatically generated code replaces the hyphen with an underline.

I had thought to use a new naming convention with my keys and have fields like "AsPOID-FK" and "VLAssetPOID-FK" sprinkled through my new project. I guess I'll switch to underline (the auto-code will comply at least; I'll have to Find all the bracketed stuff

), though it won't look as tidy.

Is it ok to have Field names with spaces? (1 Viewer)

Registered User.

Remembered

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Learning by inches ...

Super Moderator

Registered User.

Ad astra

Registered User.

Retired

Remembered

Part-time Access Guru

Similar threads

Users who are viewing this thread