469,125 Members | 1,673 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

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

200 100+
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
Expand|Select|Wrap|Line Numbers
  1. 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?
Nov 10 '09 #1
9 1786
266 Expert 100+
Without knowing what the error message is, I would say try this

Expand|Select|Wrap|Line Numbers
  1. 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.

Nov 10 '09 #2
200 100+
Expand|Select|Wrap|Line Numbers
  1. 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) "
Nov 10 '09 #3
14,534 Expert Mod 8TB
OK try this one ...

Expand|Select|Wrap|Line Numbers
  1. update DailySnapStocks 
  2. set [Date]=#4/8/2009# 
  3. where [Date]='4/8/4009' 
  4. 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.
Nov 10 '09 #4
234 Expert 100+
Remember that Date can sometimes be tricky as it's also a function.

Try this one out:
Expand|Select|Wrap|Line Numbers
  1. UPDATE DailySnapStocks SET DailySnapStocks.[Date] =#4/8/2009#
  2. 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!
Nov 10 '09 #5
14,534 Expert Mod 8TB
Great minds think alike ... and all that :D
Nov 10 '09 #6
200 100+
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?
Nov 11 '09 #7
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. update DailySnapStocks set date=DATEADD("yyyy", -2000, [Date]) where Symbol='FTSE' and date like '*40*' 
Nov 11 '09 #8
200 100+
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
Nov 11 '09 #9
14,534 Expert Mod 8TB
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.
Nov 11 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Tim Davidge | last post: by
4 posts views Thread by Tony | last post: by
1 post views Thread by John Feeley | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.