[RESOLVED] VB.NET 2003, MS Access, Need records after date, Blank Error

[circuits2]

Good morning everyone. I am working with an MS Access table that has five columns: PartNumber, Type, Qty, Assembly, and Time. Their data types are Text, Text, Number, Text, and Date, respectively. The Date column is a full time stamp, including (mm/dd/yyyy hh:mm:ss AM). I am using the following code that retrieves a number from a listbox to determine how many days back to return data (i.e. If the user wants to see the last 7 days worth of entries, then I need all entries created on and after 9/28/06.) This is then applied to a Select statement and grouped according to PartNumber. When I run this code, the Try/Catch Block kicks out a blank messagebox and no data is given. Any suggestions?

Sub DebitTabRefreshData()

        Try
            Dim topIndex As Integer = Me.uiTopListBox.TopIndex
            Dim top As Integer = Integer.Parse(Me.uiTopListBox.Items.Item(topIndex).ToString)
            Dim daysIndex As Integer = Me.uiLastListBox.TopIndex
            Dim daysSpace As Integer = Me.uiLastListBox.Items.Item(daysIndex).ToString.IndexOf(" "c)
            Dim days As Integer = Integer.Parse(Me.uiLastListBox.Items.Item(daysIndex).ToString.Remove(daysSpace, 5))
            Dim daySpan As Date = Date.Now.AddDays(0 - days)
            Dim useConString As String = _
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
                    "Source=" & Environment.CurrentDirectory & "\******.mdb;" & _
                    "Jet OLEDB:Database Password=******;"

            ' CREATE DATABASE CONNECTION AND OPEN
            useCon = New OleDbConnection(useConString)
            useCon.Open()

            ' CREATE COMMAND AND SET PARAMETERS
            useCmd = New OleDbCommand("SELECT * FROM invUsage WHERE (Time>=@time AND Type = 'DEBIT') " & _
                                        "GROUP BY PartNumber ORDER BY Qty", useCon)
            useCmd.Parameters.Add("@time", OleDb.OleDbType.Date)
            useCmd.Parameters("@time").Value = daySpan

            ' EXECUTE READER
            useRdr = useCmd.ExecuteReader

            While useRdr.Read
                Dim newItem As ListViewItem = Me.uiTopListView.Items.Add(useRdr(0).ToString)
                newItem.SubItems.Add(useRdr(2).ToString)
            End While

            ' CLOSE CONNECTIONS
            useRdr.Close()
            useCon.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Private Sub uiRefreshButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uiRefreshButton.Click

        DebitTabRefreshData()

    End Sub

[brucevde]

No idea why you get a blank messagebox but the problem is probably with the Time field. Time is a VBA function, Access assumes you are trying to call that function. Try surrounding the fieldname within [].

("SELECT * FROM invUsage WHERE ([Time]>=@time...

[circuits2]

I tried your suggestion and still got the same results. I even tried renaming the column in the database, but to no avail. Am I using the date data types correctly for VB.NET 2003?

[circuits2]

I was able to resolve this issue on my own. For those that this may help in the future, the "*" cannot be used in a SELECT statement when the GROUP BY is also used. Furthermore, the GROUP BY statement must include all columns that are returned by the SELECT statement unless the column is part of an aggregate function.

Here is my final SELECT statement:

useCmd = New OleDbCommand("SELECT PartNumber, SUM(Qty) FROM invUsage " & _
"WHERE (EntryType = 'DEBIT') AND (RecordTime >= @time) " & _
"GROUP BY PartNumber, Qty ORDER BY Qty, PartNumber", useCon)