Date Range Problem

[damini_dd]

Hello Friends,

I've designed a form which uses two DTPicker Control to select data of particular range. I want to display List of students enrolled in the period selected. I've written following code for that :

Dim dStartDate As Date
Dim dEndDate As Date
dStartDate = Format(dtpFrom.Value, "dd/MM/yyyy")
dEndDate = Format(dtpTo.Value, "dd/MM/yyyy")
With crStudList
.Connect = strConn
.SelectionFormula = "{tblStudent.RegistrationDate}>=datevalue(#" & dStartDate & "#) and {tblStudent.RegistrationDate}<=datevalue(#" & dEndDate & "#)"
' .SelectionFormula = "{tblStudent.RegistrationDate}>= DateValue (" & dtpFrom.Value & ") and {tblStudent.RegistrationDate}<= DateValue(" & dtpTo.Value & ")"
.ReportFileName = App.Path & "\reports\StudentList.rpt"
.ReportTitle = "Student List"
.WindowTitle = "Student List"
.WindowState = crptMaximized
.Destination = crptToWindow
.Action = 1
End With


where i want to display students registered between dStartDate and dEndDate. If i select start date as 01/02/2006 and end date as 28/02/2006 then its giving students registered in january also. But if i give 02/01/2006 and 28/02/2006 its giving correct result. I tried

crStudList.SelectionFormula = "{tblStudent.RegistrationDate}>=datevalue(#" & dStartDate & "#) and {tblStudent.RegistrationDate}<=datevalue(#" & dEndDate & "#)"


but no difference and getting same report. plz help me. I tried lot but not getting solution. Any quick help will be appreciated.

thx in advance

[brucevde]

When a date variable is converted to a string, the Regional Settings are used which may cause the dd/mm/yyyy format you set to be changed.

You are always better off using a non ambiguous date format like dd-MMM-yyyy.

Try changing the two Date variables to String variables and use the above format.

Dim dStartDate As String
Dim dEndDate As String

dStartDate = Format(dtpFrom.Value, "dd-MMM-yyyy")
dEndDate = Format(dtpTo.Value, "dd-MMM-yyyy")