Access query problem

Hi,

I have a c# form in which I retrieve data from Access DB.
I have Two DateTimePickers so I could choose data between dates.
My problems is when I try to retreive data between 19/10/2010 and 12/12/2010 it gives me me data beyond 12/12/2010 but when I try to retreive data between 9/12/2010 and 12/12/2010 it works fine.

here's my query:

C# Syntax (Toggle Plain Text)
"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order
 where DateValue(DateId) between "'19/10/2010' and '12/12/2010'";
 
"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order
 where DateValue(DateId) between "'19/10/2010' and '12/12/2010'";

I also tried to do it with getting the dates form the datetimepicker:

"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order
 where DateValue(DateId) between "' + datepicker1.Value.Date.ToShortDateString + '" +
" and '" + datePicker2.Value.Date.ToShortDateString +"'";
 
"select DateId, NiarDesc, ManagerId, RateText, QuantityVal, ActionID From tbl1_order
 where DateValue(DateId) between "' + datepicker1.Value.ToShortDateString + '" +
" and '" + datePicker2.Value.ToShortDateString +"'";

It seems that it ignores the month value and considers only the day value.
Why this is happening?

Thanks,
Udi

Hi,

Try like this:


OleDbConnection con=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Shahan.COREI3\My Documents\DateRange.mdb");
            OleDbCommand command = new OleDbCommand("select * from tbl1_order where StartDate between #" + dateTimePicker1.Value.ToString().Split(' ')[0] + "# and #" + dateTimePicker2.Value.ToString().Split(' ')[0] + "#");
            con.Open();
            command.Connection = con;
            OleDbDataAdapter da=new OleDbDataAdapter(command);
            DataSet ds=new DataSet("Dset");
            da.Fill(ds);
            dataGridView1.DataSource =ds.Tables[0];
            con.Close();

Check this as well:

http://www.eggheadcafe.com/community/aspnet/88/10220599/data-between-two-dates.aspx

Reagrds,
Shahan

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Shahan Ayyub15y ago
Hi,

Try like this:


OleDbConnection con=new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Shahan.COREI3\My Documents\DateRange.mdb");
            OleDbCommand command = new OleDbCommand("select * from tbl1_order where StartDate between #" + dateTimePicker1.Value.ToString().Split(' ')[0] + "# and #" + dateTimePicker2.Value.ToString().Split(' ')[0] + "#");
            con.Open();
            command.Connection = con;
            OleDbDataAdapter da=new OleDbDataAdapter(command);
            DataSet ds=new DataSet("Dset");
            da.Fill(ds);
            dataGridView1.DataSource =ds.Tables[0];
            con.Close();

Check this as well:

http://www.eggheadcafe.com/community/aspnet/88/10220599/data-between-two-dates.aspx

Reagrds,
Shahan