Runtime Error 2001

ketAccess · April 10th, 2007, 05:42 PM

Hi i am getting runtime error 2001. When i debug, the pointer goes to following line

DoCmd.OpenQuery "Search", acViewNormal, acReadOnly

I think there is a problem with query but i can't find out what it is.

here is the form code.

anybody help to solve this

Thanks
-------------------------------------------------
Option Compare Database

Private Sub Command8_Click()
   ' On Error GoTo Err_Command8_Click
    Dim mydb As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim j As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strWhere1 As String
    Dim strWhere2 As String
    Dim strIN As String
    Dim strIN1 As Long
    Dim strIN2 As Long
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
    Set mydb = CurrentDb()

    strSQL = "SELECT Sum([Rainfall].[TotalRainfall]) FROM [Rainfall]" ' ,[Rainfall].[STNumber],[Rainfall].[ID],[Rainfall].[dc],[Rainfall].[Date1],[Rainfall].[QPV],[Rainfall].[NumDay],[Rainfall].[AccND],[Rainfall].[#] FROM [Rainfall]"

    For i = 0 To LstStation.ListCount - 1
        If LstStation.Selected(i) Then
            strIN = strIN & "'" & LstStation.Column(0, i) & "',"
        End If
    Next i

    strIN1 = Text1.Value
    strIN2 = Text2.Value

    strWhere = " WHERE [STNumber] = " & Left(strIN, Len(strIN) - 1) & ""
    strWhere1 = " AND [Date1] >= " & strIN1 & ""
    strWhere2 = " AND [Date1] <= " & strIN2 & ""

    If Not flgSelectAll Then
        strSQL = strSQL & strWhere & strWhere1 & strWhere2
    End If

    mydb.QueryDefs.Delete "Search"
    Set qdef = mydb.CreateQueryDef("Search", strSQL)

    DoCmd.OpenQuery "Search", acViewNormal, acReadOnly

    For Each varItem In Me.LstStation.ItemsSelected
        Me.LstStation.Selected(varItem) = False
    Next varItem

Exit_Command8_Click:
    Exit Sub

Err_Command8_Click:

   If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list", , "Selection Required !"
        Resume Exit_Command8_Click
    Else
    'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_Command8_Click
    End If
End Sub

robzyc · April 11th, 2007, 03:43 AM

Hi ketAccess,

I think your SQL may be wrong, try:

Code:

strSQL = _
"SELECT Sum([Rainfall].[TotalRainfall]) AS [Total Rainfall]" & vbCrlf & _
"[Rainfall].[STNumber]," & vbcrlf & _
"[Rainfall].[ID]," & vbcrlf & _
"[Rainfall].[dc]," & vbcrlf & _
"[Rainfall].[Date1]," & vbcrlf & _
"[Rainfall].[QPV]," & vbcrlf & _
"[Rainfall].[NumDay]," & vbcrlf & _
"[Rainfall].[AccND]," & vbcrlf & _
"[Rainfall].[#] FROM [Rainfall]"

Also, you need to add a "GROUP BY" clause as you are using the SUM function.

Hope this helps, if you need any more, let me know.

Regards,
Rob

robzyc · April 11th, 2007, 03:44 AM

Oh, forgot to add, one thing I learned (the hard way), ALWAYS format your SQL like I did above, may not mean much to the data provider (be it JET/SQL Server) but it sure as hell makes debugging and maintenence a LOT easier!

Regards,
Rob