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.

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.

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.

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.

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]

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.

"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!)

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.

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.

--

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.