How to select data between two dates in C# using ms access

try this

da = new OleDbDataAdapter("SELECT [Column1],[Column2],[Column3],[Date],[Receipt No],[Delivery Person] from [Total] Where [Date] between #" + dateTimePicker2.Value.ToShortDateString() + "# AND #" + dateTimePicker3.Value.ToShortDateString() + "#", VCON);

If you would post your schema and some sample data that would help. Without seeing your database, which i don't have access to, i am making some likely guesses as to the root cause.

But to clarify i'll share an example you can run in sql server (i don't have access sorry).

This example is more to show the issue of time stamps in your sql query.

DECLARE @Dates TABLE 
(
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 DateExample DATETIME NULL
);

INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 20:30:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 19:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 18:30:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 16:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 15:00:32.843')
INSERT INTO @Dates ( DateExample ) VALUES ('2016-09-08 14:30:32.843')

--This returns nothing due to the time stamps being midnight and the time stamps in the data being
SELECT * FROM @Dates WHERE DateExample BETWEEN '2016-09-08 00:00:00.000' AND '2016-09-08 00:00:00.000'

--Returns results because it converts dateexample to a date, which makes the timestamps midnight.
SELECT * FROM @Dates WHERE CAST(DateExample AS DATE) BETWEEN '2016-09-08 00:00:00.000' AND '2016-09-08 00:00:00.000'

The first select statement takes into account time stamps, and since your are stripping your times off of your dates being passed in , your comparing a date at 12:00am to other dates with a time stamp which is likely the cause of you not having any data returned.

The other issue that might be arising is you are converting a date to a string and that may not jive well with access.

The second query converts the dateexample column to date which makes the timestamp 12:00am and works with that query.

Given that T-SQL doesn't convert well to MS Access you have 2 options

1) Make sure your dates in your BETWEEN clause include time stamps that meet the criteria of what you are looking for. You may need to append 00:00:00 and 24:59:59.59 to your start and end dates of your BETWEEN statement in order to get all valid data.

2) I think MS Access may have functions that convert strings to date and in turn strip the time stamp off of it. I don't have MS access installed so i can't test this but I think this is possible from a quick google search.

ms access CAST - Google Search[^]


Again, i don't have access to your DB so this may be off. If it is then feel free to provide schema and clarify your issue.

using System.Data.SqlClient;

Connection.CommandText = "Select * form table where Date between @stdate and @ldate;
Connection.Parameters.Addwithvalue("@stdate","startdate");
Connection.Parameters.Addwithvalue("@ldate","todate");