By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,663 Members | 762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,663 IT Pros & Developers. It's quick & easy.

date problem

100+
P: 121
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
Jan 6 '08 #1
Share this Question
Share on Google+
7 Replies


100+
P: 274
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")
Jan 6 '08 #2

debasisdas
Expert 5K+
P: 8,127
Try to use dtpicker instead of masked control, and format the date as per database format before passing to SQL string.
Jan 6 '08 #3

100+
P: 274
Is this useful/effective to use datepicker for applications that are not allowed to use pointing devices?
Jan 6 '08 #4

daniel aristidou
100+
P: 491
"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')
Jan 6 '08 #5

Expert 5K+
P: 8,434
... "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.
Jan 7 '08 #6

Expert 5K+
P: 8,434
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.
Jan 7 '08 #7

debasisdas
Expert 5K+
P: 8,127
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 ?
Jan 7 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.