Solved Object required (1 Viewer)

[Gasman]

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

You either do one or the other, not both.

 

[chizzy42]

And, I can't say it another time without sounding petulant.... "Try it"

Hi GPGeorge, i ran the update query

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?

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

 

[Gasman]

Why do you STILL have a recordset?

 

[GPGeorge]

Hi GPGeorge, i ran the update query

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?

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

In other words, the update query works ( as I suspected), so you went back and reran the same redundant version with a recordset?

That really was petulant. I apologize.

The point is that the query alone is adequate. There is no reason to use the recordset as well.

Rather than hard code the Number and interval in the procedure, you can pass them in as arguments and concatenate them into the SQL String:

DQRY = "Update tblDataLog Set [PM Due] = DateAdd(" & IntervalType & ", " & Number & ", [PM Due]);"

Leave out all the lines relating to a recordset.

 

[chizzy42]

Gasman, GPGeorge,bastanu,theDBguy....​

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

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

 

[Gasman]

So now you have gone to -7 from +6 ???
Is that because you added 6 on every record multiple times for as many records as existed?

 

[Pat Hartman]

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.

 

[chizzy42]

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....

 

[chizzy42]

So now you have gone to -7 from +6 ???
Is that because you added 6 on every record multiple times for as many records as existed?

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