Update Query

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.
pdvsaProject financeAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
'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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
pdvsaProject financeAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
pdvsaProject financeAuthor Commented:
Thank you.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
 
pdvsaProject financeAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
pdvsaProject financeAuthor Commented:
thank you Jim.  that worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.