[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL column name begins with a number

Posted on 2011-05-08
18
Medium Priority
?
900 Views
Last Modified: 2012-08-13
In Access, I linked to a SQL table where some of the column names begin with a number (i.e. 24K).  When I run a Select query, Access populates all the rows for those fields with the number in the column name (24).  Any solution besides renaming those columns in SQL?
0
Comment
Question by:isda-don
  • 7
  • 6
  • 4
17 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 500 total points
ID: 35715896
You could try enclosing that field/column name in square brackets, [24K].

Or using the table name when you are referrint to the field, TableName.24K, or combine the two - TableName.[24K].
0
 

Author Comment

by:isda-don
ID: 35716053
If I enter either [24K] or dbo_table.[24K] in the Field:  in the Design View, Access automatically reverts it back to 24K.  I also tried Expr1: [dbo_rio_Combo]![24K] with the same results.
0
 
LVL 35

Expert Comment

by:Norie
ID: 35716106
How exactly where you trying to use the field in the query when you got the results you describe in the first post?

If you goto SQL view you could try changing it there.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:isda-don
ID: 35716146
I am just trying to return the rows from a SQL table in an Access Select query.  There are Null values in the 24K column interspersed with numeric values, but the Select query only returns the 24.

Actually the SQL view has it as dbo_rio_Combo.[24K].

Thanks.
0
 
LVL 35

Expert Comment

by:Norie
ID: 35716373
I realise what you are trying to do.

I was trying to establish how the number 24 is ending up in the field, which I thought was what you described.

In your query are you trying to use an alias for that field? Something like this perhaps.

AliasName:[24K]
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 500 total points
ID: 35720844
While Access allows one to have a field name starting with a number or consist solely of a number, it is not good design practice.  I created an Access table tbl123 with field names 1, 2, and 3. It looks like this in table view:


1      2      3
a            2011-03-04
b      10      
      11      2011-01-02

With this query:

SELECT 1,2,3 FROM tbl123;

it returns:

Expr1000      Expr1001      Expr1002
1      2      3
1      2      3
1      2      3

With this query

SELECT [1],[2],[3] FROM tbl123;

it returns

1      2      3
a            2011-03-04
b      10      
      11      2011-01-02

I would have thought a similar pattern for a linked table??
0
 
LVL 35

Expert Comment

by:Norie
ID: 35721057
GRayL

That's what I though as well, but I don't know if the questioner has tried it - or if they have how.
0
 

Author Comment

by:isda-don
ID: 35721647
In Design View, I tried several iterations including:

24K:24K
24K:[24K]
24K:[dbo_table].[24K]
Expr1:[dbo_table]![24K]

They all returned the value 24 in all rows.
When I added the Criteria: <>24, then I got an error referring to the numeric column.

I agree with GRayl and it's not good design practice to name columns with a number or to begin a column name with a number, so I went with my fallback position - I renamed the columns in the SQL table.  It works as expected.

Thank you for your help.
0
 
LVL 35

Expert Comment

by:Norie
ID: 35722241
Did you try [24K] on it's own with no alias?

That's  what I actually meant.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35722358
This is my query in SQL View

SELECT [1], [2], [3] FROM tbl123;

This is the Query Design view of the same
Clipboard01.jpg
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35722373
I don't understand what you are trying to show us in your post at http:#a35721647 - where are those 4 examples from?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35722436
I should add that the query design view does not indicate the field name is wrapped in brackets, contrary to the SQL View.  Remove the brackets, and the Design View looks identical, but the results are definitely not.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35756277
I do not follow how you can resolve an Access issue outside of Access.  I believe I showed the asker the peril of using field names that start with a number - ie.  they must be wrapped in brackets.  My post at http:#a35722358 refers.  I believe I answered the question.
0
 
LVL 35

Expert Comment

by:Norie
ID: 35756327
GRayL

I'm not realy bothered and perhaps I didn't fully explain why but I do seem to recall mentioning the use of square brackets in a post somewhere in this thread.

Might even have been the first one.:)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35756362
Right you are.  I just don't see how this thread can be shut down as the asker intends.
0
 
LVL 35

Expert Comment

by:Norie
ID: 35756454
GRayL

I can understand that, I actually though it had been closed.

The OP seems to have stopped responding though.

If anything perhaps if points are going to be allocated they should be split.

I maybe posted the 'solution' but not much else.

What you posted, hopefully, helped the OP understand what the problem might be, why such fields names should be avoided, etc
0
 

Author Closing Comment

by:isda-don
ID: 35756906
I am sorry to both of you - imnorie and GRayL.  I agree with GRayL about his design insight (i.e. no columns as numbers) and I appreciate the attention both of you gave to my problem.  I like imnorie's idea about splitting the award points would like to do just that - 125 to each of you.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question