Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

RunSQL Insert into problem

Status
Not open for further replies.

hedgracer

Programmer
Mar 21, 2001
186
US
here is my code:

Dim sSQL As String
sSQL = "SELECT Fills_Data.SrcAccount, Fills_Data.SrcUserId, Fills_Data.Account, Sum(Fills_Data.LastQty) AS SumOfLastQty INTO ResultsTable FROM Fills_Data GROUP BY Fills_Data.SrcAccount, Fills_Data.SrcUserId, Fills_Data.Account, Fills_Data.TradeDate HAVING (((Fills_Data.TradeDate) > '" & sPreviousDate & "'));"
DoCmd.RunSQL sSQL

The data contained in sPreviousDate is '20070310'. The problem is that this creates but does not populate the table. The fills_data table is an sql server table and the access database uses an odbc connection to read it. When I try this code in an access query it works just fine. I am lost for a solution and would appreciate any help given. Thanks.

Dave
 
I guess that TradeDate is of a date data type..
Code:
sPreviousDate = left(sPreviousDate, 4) & "-" & _
                mid(sPreviousDate, 5, 2) & "-" & _
                right(sPreviousDate, 2)

sSQL = "SELECT FD.SrcAccount, " & _
          "FD.SrcUserId, " & _
          "FD.Account, " & _
          "FD.TradeDate , " & _
          "Sum(FD.LastQty) AS SumOfLastQty " & _
       "INTO ResultsTable " & _
       "FROM Fills_Data As FD " & _
       "GROUP BY FD.SrcAccount, " & _
          "FD.SrcUserId, " & _
          "FD.Account, " & _
          "FD.TradeDate " & _
       "WHERE FD.TradeDate>#" & sPreviousDate & "#;"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top