By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,386 Members | 1,669 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,386 IT Pros & Developers. It's quick & easy.

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

mikek12004
100+
P: 200
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' 
  2.  
which didn't work but is pretty much what I want to do any help?
Nov 10 '09 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 266
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.

-AJ
Nov 10 '09 #2

mikek12004
100+
P: 200
Tried
Expand|Select|Wrap|Line Numbers
  1. update DailySnapStocks set Date=#4/8/2009# where Date=#4/8/4009# and Symbol='FTSE'  
  2.  
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

MMcCarthy
Expert Mod 10K+
P: 14,534
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'
  5.  
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

topher23
Expert 100+
P: 234
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

MMcCarthy
Expert Mod 10K+
P: 14,534
Great minds think alike ... and all that :D
Nov 10 '09 #6

mikek12004
100+
P: 200
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

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...


Expand|Select|Wrap|Line Numbers
  1. update DailySnapStocks set date=DATEADD("yyyy", -2000, [Date]) where Symbol='FTSE' and date like '*40*' 
  2.  
Nov 11 '09 #8

mikek12004
100+
P: 200
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

MMcCarthy
Expert Mod 10K+
P: 14,534
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.