• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Using Docmd.RunSQL to update data

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
thandel
Asked:
thandel
  • 8
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
thandelAuthor Commented:
I got a run-time error, missing ),], or tiem in query expression.  I don't see where the error is though.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
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!

 
thandelAuthor Commented:
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
 
thandelAuthor Commented:
If I remove (Date()-TMain.DateEnter>89) then it works.
0
 
thandelAuthor Commented:
DateEnter is formated as Date/Time
0
 
thandelAuthor Commented:
DateEnter is formated as Date/Time.  All the data in this field is just a date, without any time information.
0
 
peter57rCommented:
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
 
thandelAuthor Commented:
Thanks I had the evaluation backwards.
0
 
thandelAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
thandelAuthor Commented:
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

Technology Partners: 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!

  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now