Problem with a update query

[Jack]

Hi,
I have a temp table where I need to extract the year part from a date field
and update a Year field(text type) with the extracted date. I am trying to
accomplish this by means of the following query from the sql window:
UPDATE Temp
SET Year = select DatePart("yyyy",[Date]) from Temp;

However, I cannot get this to work because of syntax error. Any help is
appreciated.
Thanks.

 

[Rick B]

YEAR is a reserved word. Come up with a different name for your field. So
is DATE.

I would have to wonder why you are doing this though. You should almost
never need a 'temporary table' since a query can be used as record source.

 

[Jack]

Thanks Rick for the help. I appreciate it. Though it is not proper to keep
the fields as Date and Year, I got the update query to work with a little
change. The way it worked is as follows:
I had to take out the select clause. Thus it worked with the following:

update temp
set year = DatePart("yyyy",[Date])
Regards

YEAR is a reserved word. Come up with a different name for your field. So
is DATE.

I would have to wonder why you are doing this though. You should almost
never need a 'temporary table' since a query can be used as record source.

--
Rick B

Hi,
I have a temp table where I need to extract the year part from a date field
and update a Year field(text type) with the extracted date. I am trying to
accomplish this by means of the following query from the sql window:
UPDATE Temp
SET Year = select DatePart("yyyy",[Date]) from Temp;

However, I cannot get this to work because of syntax error. Any help is
appreciated.
Thanks.

 

[Alex White MCDBA MCSE]

Yep what allowed this to work was the [ ] around the field name, but nice
descriptive names (that are not reserved words) make life so much easer and
your code more readable, the thing I have to remind myself is I might not be
the only one reading my code.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Thanks Rick for the help. I appreciate it. Though it is not proper to keep
the fields as Date and Year, I got the update query to work with a little
change. The way it worked is as follows:
I had to take out the select clause. Thus it worked with the following:

update temp
set year = DatePart("yyyy",[Date])
Regards

YEAR is a reserved word. Come up with a different name for your field.
So
is DATE.

I would have to wonder why you are doing this though. You should almost
never need a 'temporary table' since a query can be used as record
source.

--
Rick B

Hi,
I have a temp table where I need to extract the year part from a date field
and update a Year field(text type) with the extracted date. I am trying
to
accomplish this by means of the following query from the sql window:
UPDATE Temp
SET Year = select DatePart("yyyy",[Date]) from Temp;

However, I cannot get this to work because of syntax error. Any help is
appreciated.
Thanks.