[manaskr79]

Can somebody say what is the problem the following code?
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.
Regards.

[raziel_]

try => and <= something like this:

"Select * from EXPENDITURE where F_DATE BETWEEN =>'" & dtFDW & _ 
"' And F_DATE BETWEEN <='" & dtLDW & "'", Conn, adOpenDynamic, adLockOptimistic

This post has been edited by NoBrain: 12 September 2011 - 04:36 PM

[BobRodes]

Strangely enough, you need to enclose the dates in single quotes rather than pound signs. BETWEEN will implicitly recast this as a date and do the comparison.

[manaskr79]

BobRodes, on 14 September 2011 - 06:07 PM, said:

Strangely enough, you need to enclose the dates in single quotes rather than pound signs. BETWEEN will implicitly recast this as a date and do the comparison.

[manaskr79]

Thanks it worked