Solved

Update Query

Posted on 2013-01-04
9
270 Views
Last Modified: 2013-01-06
Experts,

I want to update the TASKS.DueDate if DueDate = Current Date.
If equals Current Date then Add 1 day.
format of [DueDate] = Short Date

I am making an update query for this.

I am not sure if I can use an IIF in the criteria section.  
If I can then I dont know the syntax.

thank you.
0
Comment
Question by:pdvsa
  • 4
  • 3
  • 2
9 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 38745033
'Assuming 'Current Date' means whatever the current date is, and not a column name

UPDATE TASKS
Set DueDate = DateAdd("d", 1, DueDate)
WHERE DueDate = Date
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38745063
<No Points wanted...>

Remember that you don't have to make a dedicated saved query.

You can run Jim's SQL like so from a button:

Dim strSQL as String
strSQL ="UPDATE TASKS Set DueDate = DateAdd('d', 1, DueDate) WHERE DueDate = Date "
Currentdb.execute strSQL, Dbfailonerror

In this way you are not creating saved queries that can be accidentally run, (or worse deleted), by accident.
This also keeps your object count low.

;-)

JeffCoachman
0
 

Author Comment

by:pdvsa
ID: 38745121
Jim thank you... That should be my answer although not at a computer now to test.

Jeff:  thanks for that tip.  I was wondering about how an expert would do that.   The button method is nice.  I will use that.  I feel obligated to award you some points for that tip.

Thank you both
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38745210
No Points wanted for me...
;-)

Jim answered your question directly here.

I just posted some extra info that Jim or any other expert could have posted...

Take it as a late Holiday present...

;-)

Jeff
0
 

Author Closing Comment

by:pdvsa
ID: 38745329
Thank you.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38745357
Thanks for the grade.  Good luck with your project.  -Jim
0
 

Author Comment

by:pdvsa
ID: 38748876
Jim / Jeff:

Finally at a computer and I am testing under the button method.
Wondering if you know why I get an error of "too few paramters" error 3061  
I am running the button from a report built on the table "TASKS" (if that makes a difference)

thanks..

error
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38749088
Since you're writing VBA to pass a T-SQL statement, gotta pass the date outside of the quotes so that it passes the date, and not text called 'Date'

strSQL = "UPDATE TASKS Set DueDate = DateAdd('d', 1, DueDate) WHERE DueDate=#" & Date() & "#"

Open in new window

0
 

Author Comment

by:pdvsa
ID: 38749478
thank you Jim.  that worked perfectly.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question