date problem

[charvi]

Hi
i want to retieve all the records of a particular date where date is specifiedin mask edbox
i have writtencode lie this

rssearch.Open "select * from timesheet where date1= #" & mskfrom.Text & "#", con, adOpenDynamic, adLockOptimistic
but it does not work though the specified date is pesent in ms- access table

[creative1]

Hi
i want to retieve all the records of a particular date where date is specifiedin mask edbox
i have writtencode lie this

rssearch.Open "select * from timesheet where date1= #" & mskfrom.Text & "#", con, adOpenDynamic, adLockOptimistic
but it does not work though the specified date is pesent in ms- access table

You need to format you date for access; May be in a variable and then pass in query
fromdate = Format$(mskfrom.Text , "m/dd/yy")

[debasisdas]

Try to use dtpicker instead of masked control, and format the date as per database format before passing to SQL string.

[creative1]

Is this useful/effective to use datepicker for applications that are not allowed to use pointing devices?

[daniel aristidou]

"select * from timesheet where date1= #" & mskfrom.Text & "#" .
Why not try formating both dates like this,
"select * From timesheet where Format(Date1,'yyyymmdd') = " & Format(mskfrom.Text ,'yyyymmdd')

[Killer42]

... "select * From timesheet where Format(Date1,'yyyymmdd') = " & Format(mskfrom.Text ,'yyyymmdd')

Generally speaking, it's not a good idea to use a function in the WHERE clause. It may not matter if the database is small, but for a large one, no way!

You force the DBMS to read every record and format the value, just to decide whether to read the record. :(

Also, in this example I think you'd need single quotes around the test value.

[Killer42]

Something else to keep in mind. Is it actually a date value stored in the field, or a date and time?

Let's say the database record has 1/1/2007 13:53:28 in the field. If you search for 1/1/2007 I would not expect it to match.

In such a case you might have to consider options such as...

• Use a range, such as Between #m/d/y 00:00:00# and #m/d/y 23:59:59#.
• Use a function (yes yes, I know what I said...) to look at just the date, without the time.

Of these two, obviously you'd expect the range to be more efficient, based on what I mentioned above.

[debasisdas]

Is this useful/effective to use datepicker for applications that are not allowed to use pointing devices?

You mean to say want to use only textbox and buttons and nothing else ?