Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

MS Access SQl query/replace


  • Please log in to reply
1 reply to this topic

#1 Casey_boy

Casey_boy

    Bleeping physicist


  • Malware Response Team
  • 7,765 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:UK
  • Local time:07:02 AM

Posted 17 August 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

If I have been helping you and I do not reply within 48hours, feel free to send me a PM.


* My Website * Am I Infected? * Malware Removal Help * If you'd like to say thanks *


BC AdBot (Login to Remove)

 


#2 Casey_boy

Casey_boy

    Bleeping physicist

  • Topic Starter

  • Malware Response Team
  • 7,765 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:UK
  • Local time:07:02 AM

Posted 27 August 2009 - 09:26 AM

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

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

If I have been helping you and I do not reply within 48hours, feel free to send me a PM.


* My Website * Am I Infected? * Malware Removal Help * If you'd like to say thanks *





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users