> 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/article.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 PROTECTED]
