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

DateValue

P: n/a
I am using Access as the reporting front end to some linked SQL2000
tables. I'm told that if I use DateValue() or "WHERE field like %" in
a query, the database engine bypasses any optimization gained through
proper data typing and indexing and does a table scan.

Is this true? Thanks in advance for your reply.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 17 Dec 2003 08:18:40 -0800 in comp.databases.ms-access,
ca*******@capecoder.com (CapeCoder) wrote:
I am using Access as the reporting front end to some linked SQL2000
tables. I'm told that if I use DateValue() or "WHERE field like %" in
a query, the database engine bypasses any optimization gained through
proper data typing and indexing and does a table scan.

Is this true? Thanks in advance for your reply.


Yes, if you use a function call on the criteria then each value has to
be read from the table and the function performed on that value in
order to determine if the result of the function meets the criteria
specified, likewise using "Like '%'", you're not giving it anything to
narrow it down, besides "Like '%'" returns all records anyway.

Using "Like 'A%'" will most likely use an index if present.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #2

P: n/a
Trevor - Thanks!!

Trevor Best <bouncer@localhost> wrote in message news:<vo********************************@4ax.com>. ..
On 17 Dec 2003 08:18:40 -0800 in comp.databases.ms-access,
ca*******@capecoder.com (CapeCoder) wrote:
I am using Access as the reporting front end to some linked SQL2000
tables. I'm told that if I use DateValue() or "WHERE field like %" in
a query, the database engine bypasses any optimization gained through
proper data typing and indexing and does a table scan.

Is this true? Thanks in advance for your reply.


Yes, if you use a function call on the criteria then each value has to
be read from the table and the function performed on that value in
order to determine if the result of the function meets the criteria
specified, likewise using "Like '%'", you're not giving it anything to
narrow it down, besides "Like '%'" returns all records anyway.

Using "Like 'A%'" will most likely use an index if present.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.