Update or replace table data for specified date range 2

[BrianLe]

In AC97, how do you update or replace data in a table for a specified date range? I have

tblMWHLongRangeRev
MWHDate (Indexed, no duplicates)
MWHUnit1ProjRev
MWHUnit2ProjRev

and frmMWHLongRangeRev that has
tbxStartDate
tbxEndDate
tbxMWHUnit1
tbxMWHUnit2
cmdAddRecord button.

When the cmdAddRecord button is clicked, I'd like the values in tbxMWHUnit1 and tbxMWHUnit2 to replace the existing values in MWHUnit1ProjRev and MWHUnit2ProjRev for the dates from tbxStartDate to tbxEndDate.

For example before update

tblMWHLongRangeRev
10/27/07 65 34
10/28/07 65 34
10/29/07 65 34
10/30/07 65 34

After update:

When tbxStartDate = 10/28/07
tbxEndDate = 10/29/07
tbxMWHUnit1 = 50
tbxMWHUnit2 = 80

tblMWHLongRangeRev
10/27/07 65 34
10/28/07 50 80
10/29/07 50 80
10/30/07 65 34

Thanks,

Brian

 

[JerryKlmns]

You create as SQL statement on the fly and you execute it against either the currently open database (=CurrentDB) if you are using DAO or the currently open database's connection (=CurrentProject.Connection) if you are using ADO

Dim strSQL As String

strSQL= "UPDATE tblMWHLongRangeRev " & _
        "SET MWHUnit1ProjRev = " & Me.tbxMWHUnit1 & ", " & _
             MWHUnit2ProjRev = " & Me.tbxMWHUnit2 & " " & _
        "WHERE  MWHDate Between #" & Me.tbxStartDate & "# AND #" & _
                                 " & Me.tbxEndDate & "#;"
'DAO 3.6
'CurrentDB.Execute strSQL
'ADO 2.x
CurrentProject.Connection.Execute strSQL

 

[BrianLe]

Jerry,

When I copy and paste your code into the event procedure the text is red. When I try to run the command I get a syntax error. Could it be related to the format of the dates. I set the format to General Date?

Thanks,

Brian

 

[PHV]

You simply have to correct the syntax errors ...

strSQL = "UPDATE tblMWHLongRangeRev " & _
        "SET MWHUnit1ProjRev = " & Me.tbxMWHUnit1 & ", " & _
        "    MWHUnit2ProjRev = " & Me.tbxMWHUnit2 & " " & _
        "WHERE  MWHDate Between #" & Me.tbxStartDate & "# AND #" & _
                                     Me.tbxEndDate & "#;"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

Brian

The openning quote of the 3rd line took a walk to the begininng of the 5th. I 'll have to punish it. Just drag it be the ear to stay put at the beginning of the 3rd and leave the 5th alone!

 

[BrianLe]

Works great. Be nice to that ".

Thanks,

Brian

 

[JerryKlmns]

Okay, if you let PHV a pinky too, since he spoted that just before I 've read it and (finalyyyyyyyyyy) submited mine

 

[BrianLe]

Glad to. He's helped me a lot recently.