4 Replies - 562 Views - Last Post: 16 September 2011 - 07:26 PM Rate Topic: -----

#1 manaskr79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 10-September 11

Error in using between statement for date value

Posted 10 September 2011 - 11:41 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Error in using between statement for date value

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 464
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Error in using between statement for date value

Posted 12 September 2011 - 04:36 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Error in using between statement for date value

Posted 14 September 2011 - 06:07 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 manaskr79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 10-September 11

Re: Error in using between statement for date value

Posted 16 September 2011 - 07:17 PM

View PostBobRodes, 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.

Was This Post Helpful? 0
  • +
  • -

#5 manaskr79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 10-September 11

Re: Error in using between statement for date value

Posted 16 September 2011 - 07:26 PM

Thanks it worked
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1