How to update a date field with sql

[breukelen]

I like to update a new created field [date]
with the fields [day],[month],[year].
I tried :

update name-of-table
set datepart("yyyy", date) = [year],
set datepart("mm",date) = [month],
set datepart("dd", date) = [day];

This is not right as I get an error message.
Apprciate your advice to do this right.

Thanks in advance ,

Gunter

 

[JimHorton]

You could do
"Update name-of-table set datefield = #" & dateserial([year],[month],[day]) & "# WHERE blah = blah"
--Jim

 

[breukelen]

Jim ,

Thanks for your reply.Sorry about my ignorance,
but can you help me what I have to write for blah = blah ?
Furthermore happy hollidays and a good 2002.

Thanks in advance for your help.

Gunter

 

[JimHorton]

Gunter,
In your first post, you had said you were updating a new created field, so I'm not sure if you're updating all records in the table or just some. If all records are having this new field initialized with the same date, then just omit the Where clause. Else the Where would be based on which fields you actually want updated. If this date is to be updated to another date in another table that can be joined in a query, you'd join the tables by the proper fields (sorry I can't help here without info on the tables and thier keys), and the sql would look like:
Update name-of-table Inner Join Table2 On name-of-table.key = table2.key set datefield = #" & dateserial(table2.[year],table2.[month],table2.[day]) & "#"
--Jim

 

[breukelen]

Jim ,

thanks for your reply.I do not need where as I want to update all the records.I made a data base where the
table is test and the field I want to update is datum(short
date);other fields are year, month, day.

This is what I wrote in SQL(copied+paste) :

update test
set datum = #" & dateserial([year],[month],[day]) & "# ;

I still get a syntax error.

regards ,

Gunter

 

[JimHorton]

Gunter,
I wrote the syntax as if it were in a code module. If you're just copying to a query SQL window, then remove the double-quotes and the #',s since dateserial returns a date type. If year,month, & day are in the same table, this all should work fine.
--Jim

 

[breukelen]

Jim ,

Thanks again ,worked fine.

best regards ,

Gunter