Solved

Using Docmd.RunSQL to update data

Posted on 2008-06-15
12
421 Views
Last Modified: 2013-11-27
I am trying to do the following:

Update Table TMain to set PendingTX in Table TMain to True when PendingTx is False, Ordered is Null Hold=False and Date()-DateEnter >89.   DateEnter, Hold, Ordered, and PendingTx are all in Table TMain.

Here is my syntax but it is not correct.

DoCmd.RunSQL "UPDATE TMain SET TMain.[PendingRx] = True _
    Where (TMain.PendingTx=False AND TMain.Ordered Is Null AND TMain.Hold=False AND (Date()-TMain.DateEnter>89)"
   

Where is my error to get this to execute correctly?

Thank you.
0
Comment
Question by:thandel
  • 8
  • 3
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21788628
here is the correction:

DoCmd.RunSQL "UPDATE TMain SET TMain.[PendingRx] = True " _
    & " Where (TMain.PendingTx=False AND TMain.Ordered Is Null AND TMain.Hold=False AND (Date()-TMain.DateEnter>89)"
    

Open in new window

0
 

Author Comment

by:thandel
ID: 21788693
I got a run-time error, missing ),], or tiem in query expression.  I don't see where the error is though.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 21788706
you have indeed a missing ) at the end:
DoCmd.RunSQL "UPDATE TMain SET TMain.[PendingRx] = True " _
    & " Where (TMain.PendingTx=False AND TMain.Ordered Is Null AND TMain.Hold=False AND (Date()-TMain.DateEnter>89) )"

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thandel
ID: 21788722
Thanks that was it, however it is not updating PendingTx to True when the condition are met.

(I corrected the variables, I originally had them wrong but it is not updating PendingTx.

DoCmd.RunSQL "UPDATE TMain SET TMain.[PendingTx] = True " _
    & " Where (TMain.PendingTx=False AND TMain.Ordered Is Null AND TMain.Hold=False AND (Date()-TMain.DateEnter>89))"
0
 

Author Comment

by:thandel
ID: 21788730
If I remove (Date()-TMain.DateEnter>89) then it works.
0
 

Author Comment

by:thandel
ID: 21788733
DateEnter is formated as Date/Time
0
 

Author Comment

by:thandel
ID: 21788842
DateEnter is formated as Date/Time.  All the data in this field is just a date, without any time information.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21788849
Are you sure you have entries more than 89 days old.
Create a select query to check..

Select  Tmain.Dateenter, Date()-TMain.DateEnter as DtDiff  Where TMain.PendingTx=False AND TMain.Ordered Is Null AND TMain.Hold=False
0
 

Author Comment

by:thandel
ID: 21790751
Thanks I had the evaluation backwards.
0
 

Author Comment

by:thandel
ID: 21790753
Are you able to tell me where I went wrong with this command?

        DoCmd.RunSQL "UPDATE TMain SET TMain.[Ordered] = Format(Now(),"" mm-dd-yy"")," & _
        "TMain.[Ref] = UCase(Forms!FTxSuccess!OrderRef)," & _
        "TMain.[PendingRx] = False" & _
        "Where (TMain.PendingTx = True AND (Date()-TMain.DateEnter>89))"
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21791064
yes: you are missing a space between the "FALSE" and the "WHERE" ..

  DoCmd.RunSQL "UPDATE TMain SET TMain.[Ordered] = Format(Now(),"" mm-dd-yy"")" & _
        " ,TMain.[Ref] = UCase(Forms!FTxSuccess!OrderRef)" & _
        " ,TMain.[PendingRx] = False " & _
        " Where (TMain.PendingTx = True AND (Date()-TMain.DateEnter>89) )"

Open in new window

0
 

Author Comment

by:thandel
ID: 21794579
Thanks, I"m trying to learn this you are very helpful but I can't believe how picky it is.    I'm used to use the Macro codes in the macro module and I am trying to use more VBA.

Thanks again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

680 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