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")