MS Access statement for selecting data within selected date

Question

I need a statement to select data from an MS Access database table.

WITHIN selected dates

I have two textboxes in my GUI called StartDate and EndDate

I want to select data within those 2 dates.

I have tried 2 methods.

The first is

" DAY(V.RegDate) between " + Start.ToString("dd") 
    + " and " + End.ToString("dd");
" and MONTH(V.RegDate) between " + Start.ToString("MM") + " and " 
    + End.ToString("MM");
" and YEAR(V.RegDate) between " + Start.ToString("yyyy") + " and " 
    + End.ToString("yyyy");

V.RegDate is the date column from the database.

But it returns me no data when I select 01/08/2010 and 01/09/2010 while there is some data at 25/08/2010.

I think that is because I chose the date separately and since the 2 dates are the same, returns me nothing

I have tried another way...

" V.RegDate between #" + Start.ToString("dd/MM/yyyy") + "# and #" _
    + End.ToString("dd/MM/yyyy") + "#";

This also return me nothing

Any Ideas????

Answer 1

This pattern works for me:

SELECT sometable.somedate
FROM sometable
WHERE (((sometable.somedate) Between #2/1/2010# And #4/1/2010#));

(in this case it's MDY, I normally prefer ISO-ish style - YYYY/MM/DD because there is no way that Access can screw that up)

Maybe you've got your date set using the American default - MDY instead of the British(?) standard DMY.