The program takes a date as input and selects all the records for that week.
To do so, I've found out the 1st day and the last day of that week containing my input date and
then used the BETWEEN sql statement using those first and last date.
But, it selects the dates beyond the range and giving incorrect result.
dtFDW = DateOfFirstDayofWeek(Weekday(CDate(dt), vbMonday), CDate(dt)) //1st day of week, dt is input date// dtLDW = DateAdd("d", 6, dtFDW) //last day of week// //This function returns the first date of week for a given date// Private Function DateOfFirstDayofWeek(intCurrentDayofWeek As Integer, WhichDate As Date) As Date On Error GoTo error_handler DateOfFirstDayofWeek = DateAdd("D", intCurrentDayofWeek * (-1) + 1, WhichDate) Exit Function error_handler: DateOfFirstDayofWeek = Date End Function Set rec = New Recordset rec.Open "Select * from EXPENDITURE where F_DATE BETWEEN #" & dtFDW & _ //F_DATE is the date field in database// "# And #" & dtLDW & "#", Conn, adOpenDynamic, adLockOptimistic Do Until rec.EOF Debug.Print rec.Fields!F_DATE rec.MoveNext Loop rec.Close
I'm getting the 1st and last day of week correctly but this code is not selecting between the desired range.
I'm using MS Access 2002.