SQL: Select a column name "Short Name"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I cannot select a column if it has a space.

------------------------------------------------------------------------------------------
For example, i have a table:

Short name Code Year_Produced
ABA 1001 2005
BBB 1544 2001
CCC 1002 2000
------------------------------------------------------------------------------------------


I cannot select "short name" because there's a space in between

While i can select "Year_Produced" , because there's underscore("_") in
between.


------------------------------------------------------------------------------------------


How can I write an SQL to select name with space in between. Using quote ?
How ?

please kindly show me an example !

thank you , expert !!
 
In Access SQL you use square brackets if the column name has a space, is a
reserved word, or uses characters other than the Underscore, Letters, and
numbers.

SELECT [Short Name], Code, Year_Produced, [22], [Time], [All-Source]
FROM SomeTable

Explanation of why brackets.
Short Name has a space
22 is all numbers
Time is a reserved word (current system time)
All-Source is hyphenated and would be seen as a math expression (subtract
column "Source" from column "All") and ALL is a reserved word also.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
My advise, never put spaces in the names of anything in Access. If you feel
you need the space for readability, use an _ to separate words. You can
select the field by wrapping it in brackets.

SELECT [Short name], Code, Year_Produced
FROM yourTable

HTH
Dale
 
Perfect explaination, Straight to the answer . Thank you !


--
Allen Phailat Wongakanit


John Spencer said:
In Access SQL you use square brackets if the column name has a space, is a
reserved word, or uses characters other than the Underscore, Letters, and
numbers.

SELECT [Short Name], Code, Year_Produced, [22], [Time], [All-Source]
FROM SomeTable

Explanation of why brackets.
Short Name has a space
22 is all numbers
Time is a reserved word (current system time)
All-Source is hyphenated and would be seen as a math expression (subtract
column "Source" from column "All") and ALL is a reserved word also.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

ali said:
I cannot select a column if it has a space.

------------------------------------------------------------------------------------------
For example, i have a table:

Short name Code Year_Produced
ABA 1001 2005
BBB 1544 2001
CCC 1002 2000
------------------------------------------------------------------------------------------


I cannot select "short name" because there's a space in between

While i can select "Year_Produced" , because there's underscore("_") in
between.


------------------------------------------------------------------------------------------


How can I write an SQL to select name with space in between. Using quote ?
How ?

please kindly show me an example !

thank you , expert !!
Click to expand...
Click to expand...
 
Can you be clearer? What is the field name (column name) that you can't
specify?

Normally if the field name contains characters other than letters, numbers,
and underscores, then you refer to the field as bracket Field Name bracket
and to fully refer the name you use bracket table name bracket dot bracket
field name bracket.

[Some Table #].[Some field?]

Or in some cases (only one table in a query)
[Some field?]



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top