Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Update Query

Posted on 2013-01-04
9
276 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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

840 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