Cann't get values in a Date/Time column to increment by a set number of years

[mikek12004]

I have a column date with 'Date/Time' type which holds values like this
8/4/2009 for the 4th day of the 8th month of 2009, due to some bug many records write instead 8/4/4009 so I want for those record to put the write date. The whole thing is for a asp script so for there I could easily write a SQL query like

update DailySnapStocks set Date='4/8/2009' where Date='4/8/4009' and Symbol='FTSE' 
 

which didn't work but is pretty much what I want to do any help?

[ajalwaysus]

Without knowing what the error message is, I would say try this

update DailySnapStocks set Date=#4/8/2009# where Date=#4/8/4009# and Symbol='FTSE' 

If this is not it, please post the error, or tell us if there isn't one.

-AJ

[mikek12004]

Tried

update DailySnapStocks set Date=#4/8/2009# where Date=#4/8/4009# and Symbol='FTSE'  
 

got a "Syntax error in UPDATE statement" popoup and when I press help I see
"Syntax error in UPDATE statement. (Error 3144) "

[MMcCarthy]

OK try this one ...

update DailySnapStocks 
set [Date]=#4/8/2009# 
where [Date]='4/8/4009' 
and Symbol='FTSE'
 

Date is a reserved word in Access and should not be used on its own as a field name. Using the square brackets will help with this but I would recommend changing the field name.

[topher23]

Remember that Date can sometimes be tricky as it's also a function.

Try this one out:

UPDATE DailySnapStocks SET DailySnapStocks.[Date] =#4/8/2009#
WHERE (((DailySnapStocks.[Date] = #4/8/4009#) AND ((DailySnapStocks.Symbol)='FTSE'));

This is a bit more wordy, since I built it in the Query Editor.

EDIT: Looks like we posted on top of each other, Mary!

[MMcCarthy]

Great minds think alike ... and all that :D

[mikek12004]

Well...msquared's solution got me an 3464 Error
topher23's Worked (with removing an extra parenthesis),
a new (better) new possible solution which saw and deals with all updates at once is

update DailySnapStocks set date=DATEADD(yyyy, 1000, DailySnapStocks.date) where Symbol='FTSE' and date like '%40%'

This is MSSQL code which worked perfect in SQL server '08 but in Access
made like this

update DailySnapStocks set date=DATEADD(yyyy, 1000, [Date]) where Symbol='FTSE' and date like '*40*'

got a 3144 Error any ideas?

[MMcCarthy]

Try this ...

update DailySnapStocks set date=DATEADD("yyyy", -2000, [Date]) where Symbol='FTSE' and date like '*40*' 
 

[mikek12004]

Worked like this

update DailySnapStocks set [date]=DATEADD("yyyy", -2000, [Date]) where Symbol='FTSE' and [date] like '*40*'

At last I can continue, you cannot imagine how many hours I spent wondering why it is not working (I thought that the SQL view was identical to the mssql which I worked in sql studio since they are both from microsoft...)
Anyway thanks a lot both of you for your quick replys

[MMcCarthy]

Glad you got it working. Unfortunately the syntax in Access is quite different to MS SQL outside of the basics of course. You'll get there eventually with trial and error.