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

Function criteria in query is so slow

P: n/a
I am running Access 97 on a Windows XP system.
I run a query on an ODBC connected file that selects records for a
particular date.
I can hard code the selection criteria eg #25/03/2004# or get it from
a form eg Forms!myform!mydate or use a function get_mydate() and the
query runs as quickly no matter what the method.

However I have just run the same query on Access 2002 on a Windows XP
system and the one with get_mydate() is so slow (I think it actual
hangs) where as the other 2 are as quick as before.

Is there a known problem?
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Good question, Mike.

I have not seen documention on this issue, but I can confirm from experience
that a function call in a query is executed *much* more slowly now than it
was in A97, and the problem is not limited to ODBC connections.

If anyone has documentation on this, it would be good to read it. If not,
has anyone raised the issue with MS?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Ridley" <gi******@hotmail.com> wrote in message
news:e5**************************@posting.google.c om...
I am running Access 97 on a Windows XP system.
I run a query on an ODBC connected file that selects records for a
particular date.
I can hard code the selection criteria eg #25/03/2004# or get it from
a form eg Forms!myform!mydate or use a function get_mydate() and the
query runs as quickly no matter what the method.

However I have just run the same query on Access 2002 on a Windows XP
system and the one with get_mydate() is so slow (I think it actual
hangs) where as the other 2 are as quick as before.

Is there a known problem?

Nov 12 '05 #2

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
Good question, Mike.

I have not seen documention on this issue, but I can confirm from experience
that a function call in a query is executed *much* more slowly now than it
was in A97, and the problem is not limited to ODBC connections.

If anyone has documentation on this, it would be good to read it. If not,
has anyone raised the issue with MS?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

--- SNIP ---
This is such a disaster, that I must have missed something.

I have just ran a query on an ODBC table with just two fields (which
together make up the primary key). The selection criteria for the
first of these fields is get_mydate(). There is so much network
traffic it must be pulling the whole file to the local machine before
it evaluates the get_mydate() function.

This means I will have to change most of my queries to querydefs and
run them in a VB module.

I can't believe that other people out there aren't also screaming and
shouting about the "new and improved" Access 2002.
Nov 12 '05 #3

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
Good question, Mike.

I have not seen documention on this issue, but I can confirm from experience
that a function call in a query is executed *much* more slowly now than it
was in A97, and the problem is not limited to ODBC connections.

If anyone has documentation on this, it would be good to read it. If not,
has anyone raised the issue with MS?

Rats! Spoke too soon!

My get_mydate() function had no return value assigned so it returned a
variant.

According to Microsoft (problem 821936) this is not good when working
with ODBC tables.

I have defined the function as "function get_mydate() as Date" and it
works like magic.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.