Hey, rudy, I think for one we solved an SQL problem without you! I am actually using option 3, which as you mentioned is a bit slow. Which option do you recommend then, none of them I suppose! I'd really like to get that code faster since it ia an app that logs all the calls received by the receptionist here. And by user/pass login, each user has access to the list of people who called him/her during his/her absence. So basically, it will be run VERY often on the intranet and by many users simultaneously.
Paul > -----Original Message----- > From: rudy [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 12, 2003 2:45 PM > To: [EMAIL PROTECTED] > Subject: [wdvltalk] RE: Select Query in Access > > > > Ok, I solved my own issue. > > excellent > > i love it when an sql problem is solved before i get up (it's > 6 am here now) > > please allow me to make a few comments > > > (1) > > >=[selectedDate] AND <[selectedDate+1moreDay] > > works, if selectedDate is a constant and not your date > column, otherwise this is an expression on the date column > and therefore cannot use an index (if any) > > > (2) > > LIKE [selecteddate%] > > (this option needs converting the months to concatenated > string, i.e. > > April to apr, since LIKE makes string comparisons) > > extremely ugly, plus it requires an string conversion, so > again, no index > > > (3) > > YEAR(call_date) = '2003' AND > > MONTH(call_date) = '8' AND > > DAY(call_date) = '11' > > works, but clumsy, and requires an expression, so no index > > > (4) > > DATEPART(YEAR, call_date) = '2003' AND > > DATEPART(MONTH, call_date) = '04' AND > > DATEPART(DAY, call_date) = '09' > > clumsier than (3), not the exact access syntax, and requires > an expression > > > CAST(FLOOR(CAST([DateField] AS FLOAT))AS DATETIME) = 'selectedDate' > > worse than (4) and won't work in access, may work in sql > server, but ugly and slow > > > > Anyway, option 1 and 3 work marvelously. I prefer option 3 though! > > wait'll you try 3 on a high-volume table, it will work but very slowly > > > > What happens if you replace the 8/11/2003 with 11-Aug-2003? > > > > International date settings are problematic in Access > > no, that's not true -- well, not if you actually use the > international standard > > ISO standard date format: yyyy-mm-dd > > access has no problem with 2003-08-11, no matter what its > locale or regional settings or whatever -- this date format > will *always* be interpreted correctly > > > I always use the dd-mmm-yyyy format. > > (Purists may disagree, but it works.) > > oh, i don't doubt that it works for you, but it's pretty > awkward for someone whose version of access isn't english, or > who has to look up the english month names in order to use them > > > Also try DateDiff("d",call_date,#8/11/2003#)=0 > > in case you have a date and time in there. > > that indeed seemed to be paul's problem (the time portion) > > however, datediff applies an expression to the column and so > cannot use an index > > > > > http://databasejournal.com/features/mssql/arti> cle.php/10894_2209321_2 > > not bad > > the warning about wrapping the date column in a function > making an index unusable should have come at the front of the > article, not the end > > > > Access itself will let you set up a datetime column as date only > > really? can you give an example? pointers to relevant > documentation? > > my understanding is that a datetime column always has a time portion > > i've been wrong before, though (especially as it concerns microsoft > products) > > > rudy > > > > ____ . The WDVL Discussion List from WDVL.COM . ____ > To Join wdvltalk, Send An Email To: > mailto:[EMAIL PROTECTED] > Send Your Posts > To: [EMAIL PROTECTED] > To set a personal password send an email to > [EMAIL PROTECTED] with the words: "set WDVLTALK > pw=yourpassword" in the body of the email. To change > subscription settings to the wdvltalk digest version: > http://wdvl.internet.com/WDVL/Forum/#sub > > ________________ http://www.wdvl.com _______________________ > > You are currently subscribed to wdvltalk as: > [EMAIL PROTECTED] To unsubscribe send a blank email to > %%email.unsub%% > > ____ • The WDVL Discussion List from WDVL.COM • ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To set a personal password send an email to [EMAIL PROTECTED] with the words: "set WDVLTALK pw=yourpassword" in the body of the email. To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
