Problem with using numbers as Field names in Access? 1

[Perilous1]

I have an access database that has 10 fields label '0' thru '9'. When I set up a sql query to pull data from these fields rather than getting the field names listed correctly in the DataEnvironment it lists the 10 fields as Expr 1000 thru Expr 1009.

These are not the only fields in the Table else I'd simply do a Select *. So, I acutally have the query written as "Select 0,1,2,3,4,5,6,7,8,9 From Hannah Where (date = sDate)

If I do a Select * it reflects the fields correctly but when I individualize them I get Expr 1000-1009 and the app errors out saying it cannot find the name/ordinal.
Any help with this would be appreciated.

 

[HarleyQuinn]

Using numbers as a field name is generally a bad idea. As you've seen it confuses Access as numbers can be used as column ordinals to work with rather than the column names and if you use them as you are, it will cause problems if you need to include the columns in a WHERE clause. Also I'm not sure the data your columns are currently returning will be correct, I'd check it.

The short term fix is to wrap your column names in [] e.g.

SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9]
FROM tblNumbers;

The long term fix would be to change your columns to something more meaningful.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Perilous1]

The brackets worked, thank you!