If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can a field name start with a number?
I have a table. My column field names are like 50xs50, 100xs100, etc. By
when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? Don donwiss at panix.com. |
#2
|
|||
|
|||
It's the quotes.
-- Brendan Reynolds (MVP) "Don Wiss" wrote in message ... I have a table. My column field names are like 50xs50, 100xs100, etc. By when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? Don donwiss at panix.com. |
#3
|
|||
|
|||
On Mon, 31 Jan 2005, Brendan Reynolds brenreyn at indigo dot ie wrote:
Don Wiss wrote: I have a table. My column field names are like 50xs50, 100xs100, etc. By when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? It's the quotes. But without the quotes, e.g.: Select Year,100xs100 from RLLagMatrix; I get the error: Run-time error '3075': Syntax error (missing operator) in query expression '100xs100'. Don donwiss at panix.com. |
#4
|
|||
|
|||
You must use square brackets around the field name if it starts with a
number: SELECT Year, [100xs100] FROM ... BTW, Year() is a function name in Access, so not a good choice of field name either. It will work in the query, but is likely to give you problems if you create a form, and a text box named Year, and then refer to it in code. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Don Wiss" wrote in message ... I have a table. My column field names are like 50xs50, 100xs100, etc. By when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? Don donwiss at panix.com. |
#5
|
|||
|
|||
On Sun, 30 Jan 2005 20:07:44 -0500, Don Wiss
wrote: I have a table. My column field names are like 50xs50, 100xs100, etc. By when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? Don donwiss at panix.com. Nonstandard table or fieldnames in Access should be delimited by [brackets] not by 'quotes' (in contradistinction to SQL/Server or Oracle). Try SELECT Year, [100xs100] FROM RLLagMatrix; John W. Vinson[MVP] |
#6
|
|||
|
|||
On Mon, 31 Jan 2005, Allen Browne wrote:
Don Wiss wrote: I have a table. My column field names are like 50xs50, 100xs100, etc. By when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? You must use square brackets around the field name if it starts with a number: SELECT Year, [100xs100] FROM ... Thanks. That works fine. BTW, Year() is a function name in Access, so not a good choice of field name either. It will work in the query, but is likely to give you problems if you create a form, and a text box named Year, and then refer to it in code. Thanks for pointing this out, but it won't be a problem for me. I use Access solely as a database for my Excel programs to access. Usually with sql queries. Don donwiss at panix.com. |
#7
|
|||
|
|||
"Don Wiss" wrote in message
... On Mon, 31 Jan 2005, Allen Browne wrote: Don Wiss wrote: I have a table. My column field names are like 50xs50, 100xs100, etc. By when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? You must use square brackets around the field name if it starts with a number: SELECT Year, [100xs100] FROM ... Thanks. That works fine. BTW, Year() is a function name in Access, so not a good choice of field name either. It will work in the query, but is likely to give you problems if you create a form, and a text box named Year, and then refer to it in code. Thanks for pointing this out, but it won't be a problem for me. I use Access solely as a database for my Excel programs to access. Usually with sql queries. You could still have a problem, since Excel has a Year function as well. To be safe, enclose the word Year in square brackets as well: SELECT [Year], [100xs100] FROM ... -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
#8
|
|||
|
|||
Don
Is there a chance you've imported an Excel "database", using the column names (e.g. [100xs100], [50xs50])? I ask because it seems possible that your field names in Access include data, which will give you headaches down stream... Since I don't know what your [100xs100] refers to, I'm only wondering. Another example of fieldnames with data embedded might be [January2005], [February2005], ... Good luck Jeff Boyce Access MVP "Don Wiss" wrote in message ... I have a table. My column field names are like 50xs50, 100xs100, etc. By when I issue a sql query like: Select Year,'100xs100' from RLLagMatrix; It give me the years and a column of 100xs100s. So are these invalid field names? Don donwiss at panix.com. |
#9
|
|||
|
|||
John Vinson wrote: Nonstandard table or fieldnames in Access should be delimited by [brackets] not by 'quotes' (in contradistinction to SQL/Server or Oracle) Note SQL Server supports the MS-proprietary square brackets in addition to the ANSI standard double quotes Chr$(34). Jamie. -- |
#10
|
|||
|
|||
Jeff Boyce -DISCARD_HYPHEN_TO_END wrote:
Is there a chance you've imported an Excel "database", using the column names (e.g. [100xs100], [50xs50])? My Access tables are almost always imported from an Excel worksheet. A few are imported from comma delimited files. For my needs Access is nothing but a repository for Excel. Any updating is done in Excel, or in the program that generates the CDF files, and then reimported. I ask because it seems possible that your field names in Access include data, which will give you headaches down stream... Since I don't know what your [100xs100] refers to, I'm only wondering. Another example of fieldnames with data embedded might be [January2005], [February2005], ... I don't know what you mean by including data. The user is selecting insurance layers. The column headings/field names (with spaces compressed out) match what is available in a drop down combo box. Don donwiss at panix.com. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Text Form Field Ref in Footer Won't Update on Screen | StarWine | General Discussion | 3 | December 6th, 2004 06:17 PM |
query a number stored as text | Lee | Running & Setting Up Queries | 19 | October 13th, 2004 04:10 AM |
Fractional Number Value in a field | Maria K | Using Forms | 2 | June 22nd, 2004 12:39 AM |
increment number field | [email protected] | Database Design | 2 | April 29th, 2004 11:29 PM |
Extract variable number of chars from variable start position? | Ann Scharpf | Worksheet Functions | 9 | February 25th, 2004 02:10 PM |