Problem with Finding Records on Dates

[softhoney]

Hi


I am a new user in VISUAL BASIC.

I have a form which I placed 2 DTPicker for dates

The structure of my table is

Field Type

startdate ( Date )
amount ( Number )


The name of DTPicker component is as a sdate and edate

Inside the form I used this code


dummy.Source = "select * from payments where startdate >= #" & sdate & "# and startdate <= #" & edate & "# order by startdate"
dummy.Open



If dummy.BOF = True And dummy.EOF = True Then
MsgBox "Record Not Found"
Exit Sub
Else
List1.AddItem "--------------------------------------------"
List1.AddItem sdate & " to " & edate
List1.AddItem "--------------------------------------------"

While Not dummy.EOF
Me.List1.AddItem dummy.Fields("startdate")
dummy.MoveNext
Wend
End If


If I select the dates from dtpickers like sdate="02/01/2009" and edate="04/01/2009"
its give me the messge record not found instead of in the table there is a record.

If I select the dates from dtpickers like sdate="04/01/2009" and edate="04/01/2009"
its give me the again same messge record not found although there is a record in the table.Its should find the record but its give me the nothing

Please help me in this matter.

Thanks

[Ron Weller]

If your database only has the date in it, no time part, then you may need to
strip off the default time part of each date. But if your database does have
date and time parts you may need to strip off the default times and hard
code the min and max time parts.

'just remove the time part
dummy.Source = "select * from payments where startdate >= #" & Fix(sdate) & "# and startdate <= #" & Fix(edate) & "# order by startdate"

'remove and then hard code the time parts
dummy.Source = "select * from payments where startdate >= #" & Fix(sdate) & " 00:00:00# and startdate <= #" & Fix(edate) & " 23:59:59# order by startdate"

FYI: Dates are stored as numbers where the whole part is the date and the
decimal part it the time, so using the Fix() function truncates the decimal
part, the time, making it just a date.

[softhoney]

Thanks for the reply. I tried to both queries with fix function but the result is same nothing to change.

[Hack]

What is "dummy"?