Can a field name start with a number?

[Don Wiss]

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>.

 

[Brendan Reynolds]

It's the quotes.

 

[Don Wiss]

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>.

 

[Allen Browne]

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.

 

[John Vinson]

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]

 

[Don Wiss]

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>.

 

[Douglas J. Steele]

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 ...

 

[Jeff Boyce]

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]

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>.

 

[Jeff Boyce]

Don

It sounds like you and your users do your serious work in Excel. Simply
importing Excel data, without normalization, means you won't be able to use
the built-in features and functions in Access that expect and depend on
relational data.

If this isn't a problem, no worries. But now you have me curious why you
want to import into Access, if Excel is your workhorse?

Jeff Boyce
<Access MVP>

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>.

 

[Don Wiss]

If this isn't a problem, no worries. But now you have me curious why you
want to import into Access, if Excel is your workhorse?

Some of the databases are quite large. Having them in the workbook would
make the workbook enormous and unwieldy.

Then one wants independence between the parameters and the user's input,
which gets saved along with the workbook. Allows existing data to use newer
parameters.

Then it seems that one can read in a record just as fast, or faster, than
doing a match to find which row on a sheet, and then reading in that row so
the macro can work on it.

Don <donwiss at panix.com>.