HI GpGeorge just to clarify im trying to update the entire date values in the column by a certain amount by a certain factor of days, its a database i made up years ago for maintenance and was looking to upgrade it to use again. [PM Due] is the date column in the table tbDataLog. i was nt sure if this is the way or try to use the for loop method
Update tblDataLog Set [PM Due] = DateAdd(IntervalType, Number, [PM Due]);
and filled in the d requirement and number of days and it does update ok, so that helps me in that i can forward the dates but when i try to run it as the vba function, i now get run timerror 3061 too few paramaters : expected 2. from the code below. the sql when ran standalone works fine and asks for two parameters but doesnt see it running vba?
Code:
Dim sql As String
Dim DQRY As String
Dim data1 As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim IntervalType As String
Dim Number As Integer
Number = 6
IntervalType = "d"
sql = "Select * FROM tblDataLog"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)
Do Until rst.EOF
DQRY = "Update tblDataLog Set [PM Due] = DateAdd(IntervalType, Number, [PM Due]);"
MsgBox (DQRY)
dbs.Execute DQRY
rst.MoveNext
Loop
Update tblDataLog Set [PM Due] = DateAdd(IntervalType, Number, [PM Due]);
and filled in the d requirement and number of days and it does update ok, so that helps me in that i can forward the dates but when i try to run it as the vba function, i now get run timerror 3061 too few paramaters : expected 2. from the code below. the sql when ran standalone works fine and asks for two parameters but doesnt see it running vba?
Code:
Dim sql As String
Dim DQRY As String
Dim data1 As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim IntervalType As String
Dim Number As Integer
Number = 6
IntervalType = "d"
sql = "Select * FROM tblDataLog"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)
Do Until rst.EOF
DQRY = "Update tblDataLog Set [PM Due] = DateAdd(IntervalType, Number, [PM Due]);"
MsgBox (DQRY)
dbs.Execute DQRY
rst.MoveNext
Loop
Thanks for your time and patience on this, i got well confused on this one and its working now how id like it, i went to try python and ended up with a mental block with vba....need to brush up on dao
Code:
Private Sub Command0_Click()
Dim DQRY As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
DQRY = "Update tblDataLog Set [PM Due] = DateAdd('d', -7, [PM Due]);"
dbs.Execute DQRY
End Sub
The code would be different with Python but the concept would be the same. You would just run an update query. There is no loop involved and since you want to update every row, there is no criteria required for the query.
I would like to mention however that update queries without criteria are extremely rare. They are used to correct errors but rarely for any other reason. If you have to do this in the normal course of business, your design is flawed and you should NOT be storing this date at all.
The code would be different with Python but the concept would be the same. You would just run an update query. There is no loop involved and since you want to update every row, there is no criteria required for the query.
I would like to mention however that update queries without criteria are extremely rare. They are used to correct errors but rarely for any other reason. If you have to do this in the normal course of business, your design is flawed and you should NOT be storing this date at all.
thanks for the info, i was just looking at a preventative database i did several years ago and was looking to update the dates in the due column to try it out , it was just a bulk update to realign the dates to play with the database again ie have the maintenance due dates spread over the next few months, the db itself updated the dates automatically once complete...i just just took a complete blank with the task....must be the heat here not used to it. I was primarily learning python from the ground up to try and learn how to code properly instead of the lines and lines of code i tend to do....
HI, i was just playing around there with the dates and that was the last number, they were actually several years overdue, now its all good and up to date...hurrah