[2005] Datetimerpicker+Access 2000 sql statement

[Wulfgur]

Hey,

I've run into a problem with extracting information from a database and inserting it into a listview. I am unsure if I am using the easiest way.

Problem: I have 2 DateTimePickers and a button. One datetimepicker is for the start date, the other for the finish date. When the button is clicked by the user, I want to check the database for all transactions that have been made between the start date and the finish date. Hope that makes sense, here is the code so far.

Controls:
BtnDatesearch = Button user will click
Startdate = Datetimepicker
Finishdate = datetimepicker

      Private Sub BtnDateSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDateSearch.Click
        Connection.CreateCommand()
        Command.CommandText = "SELECT * FROM Transactions WHERE Transaction_Date > " & StartDate.Text & " AND Transaction_Date < " & FinishDate.Text & ""
        Reader = Command.ExecuteReader

        While (Reader.Read)
            Dim value3 As Double = Reader.GetValue(3)
            Transactions.Items.Add(New ListViewItem(New String() {Reader.GetValue(0), Reader.GetValue(1), Reader.GetValue(2), value3.ToString("n2"), Reader.GetValue(4), Reader.GetValue(5)}))
        End While
        Reader.Close()
    End Sub

I am not sure if you are able to search in-between 2 date values the way I attempted to, is there another way to do this?

Another quick question if you don't mind.
In Access 2000, how can I change the date format from American to Australian?

Thanks

[stanav]

With Access database you have to enclose your date literal with the # signs. Thus you select statement should look something like this

"SELECT * FROM Transactions WHERE Transaction_Date > #" & StartDate.Text & "# AND Transaction_Date < #" & FinishDate.Text & "#"

However, I strongly recommend you to use parameters in your query instead of string concatenation as it is now.

[stimbo]

I don't know much about access or DB's but I presume Transaction_Date is a column with a Date/Time data type?

Anyway, should you be using the .Value property of the dateTimePicker rather than the Text property of the DTP:
e.g. StartDate.Value

[Wulfgur]

Thanks for the replies,

Not sure if I am getting the operators correct, since the code is still not working. I coded it to find a transaction on a certain day and that worked.

start = startdate.value (DateTimePicker)
finish = finishdate.value (DateTimePicker)

This code (Below) worked fine.

Command.CommandText = "SELECT * FROM Transactions WHERE Transaction_Date = #" & start & "#"

However this code didn't:

Command.CommandText = "SELECT * FROM Transactions WHERE Transaction_Date > #" & start & "# AND Transaction_Date < #" & finish & "#"

Transaction_Date is in the table 'Transactions' and is a field of data type Date/Time.

Thanks.

[techgnome]

because you are looking for dates greater than.... rather than greater or equal to...you just need to add a couple of equal signs in there.

Transaction_Date >= #" & start & "# AND Transaction_Date <= #" & finish

-tg

[Wulfgur]

Thanks for the help, seems to be working fine at the moment.