BleepingComputer.com > MS Access SQl query/replace

Full Version: MS Access SQl query/replace

BleepingComputer.com > Software > Business Applications

Casey_boy

Aug 17 2009, 07:51 AM

Hi All,

I was wondering if there's anyone who can help me out here.

I have created an Access database with tables which have a due date (maintenance) field. Presently, the user updates this field when the maintenance has been carried out to indicate the new due date (say a month or a year down the line).

What I have been asked to do though is to make it so that the database automatically updates itself. I.e. when the due date is passed the field will update depending upon the criteria for the next due date. (I know this leads it open that if a task doesn't get done it'll get ignored but I have been told that this would not happen!)

So I thought I would do:

A query on the field [Due Date]
If/else criteria: [Due Date]+30 > date() (this is to prevent items which may be quite a bit out of date remaining out of date indefinitely)
- If [Due Date]+30 > date() then replace [Due date] with [Due Date]+30.
- If [Due Date]+30 <(=) date() then loop round until [Due Date]+30 > date() criteria is met

However, I'm not really that profficient with SQL code and this is definitely beyond me. (for example, how do I do the replace so that the date is updated?)

Is creating a query the right way to go? Since I'm sure I can't do this sort of thing with the table alone? Also, if I have multiple tables which each require a different criteria [Due Date]+x can this all be combined or do I need to write out seperate queries (since I know union queries cannot be used to modify data?)

Cheers,

Casey

Casey_boy

Aug 27 2009, 09:26 AM

Ok, so just in case anyone reads this; this is how I did it:

CODE

    Dim answer
    answer = MsgBox("Are you sure? This will overwrite all data!", 32 + 1, "Confirmation")
    If answer = 1 Then

    Dim LResponse As Integer
    Dim db As Database
    Dim rec As Recordset

    Set db = CurrentDb()
    Set rec = db.OpenRecordset("Table A", dbOpenTable)

     rec.MoveFirst
     Do While Not rec.EOF


          Do While rec.Fields("Due Date") <= Date
          rec.Edit
          rec.Fields("Due Date") = DateAdd("ww", 1, rec.Fields("Due Date"))
          rec.Update
          Loop

      rec.MoveNext

    Loop

LResponse = MsgBox("Database updated successfully", 0, "Task Completed")

    Else: End
    End If

Just repeat the middle section from Set rec = db.OpenRecordset() to Loop for multiple tables.

Casey

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.