Re: function slow in query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks Robert,

I was hoping that there would be something I was missing however I have now
a few things to try with your suggestions. The initializing of a global
variable would seem a good option - I see if it speeds it up.

Tx
Grd

"Robert Morley" wrote:

This becomes problematic no matter how you look at it. :)

If you move x out to a module-level variable, or make it static, then the
value of x will be retained between calls, and you can simply check to see
if it's been assigned previously and go get the value from the form if not.
Unfortunately, this creates the problem that x will never be UNinitialized
after the first time, unless you have a convenient place to do that in your
code.

You can TRY passing the Form value to your function from the query...the
query logic *might* be smart enough to only read it once; I'm not entirely sure.

Another way to do it would be to have your form initialize a global variable
with the appropriate field value, then clear it when the form closes (or
whatever else should cause the value to reset).

The final, and generally bad, way to do it is to initialize x based on a
Timer check. If it's been more than a certain amount of time since it was
last retrieved from the form, then initialize it.

'Module-level declaration
Dim t As Single
Dim x As Long

Public Function FiscalYear(ByVal TableDate) As Integer
If Timer - t >= 5 Then
'Re-initialize x every 5 seconds
x = Forms!frmMyForm!txtBox.Value
t = Timer
End IF
'Rest of code
End Function


Rob

Grd wrote:
Hi,

I have a custom function called FISCALYEAR (TableDate) which I created to
use in a query. It takes a single argument which is a field from a table. I
use this function in a query to determine if the TableDate falls into the
fiscal year. It returns true or false.

The query using this function for one of the columns ran quickly and I was
happy.

I realized later that the function was missing a bit of information and I
needed to refer to a value on a form so I added that to logic of the
function. e.g. Dim x and x= forms!frmMyForm!txtBox.Value.

Now, unfortunately, the query that uses the function runs slowly because of
this change - it is looking up this info from the form everytime the function
is run for each record in the query.

Is there a way to get the value once - like a constant - to stop the
function checking the form for it each time?

Hope I've explained this well! I just disappointed that it slowed everything
down so much but I can't think of a way out of this problem

any help or guidance is greatly appreciated

Thanks

Grd


.

Relevant Pages

  • Re: add field to back-end existing table
    ... them a file or a batch file to add the new fields automatically. ... something you can accomplish by using an Update Query on that table. ... Then initialize the column using an Update ...
    (microsoft.public.access.tablesdbdesign)
  • Form updates table when it should not
    ... have created an afterUpdate event which then runs a query with selected ... When I open the form, it has data from previous query. ... I tried to initialize in load event, ... DoCmd.OpenForm stDocName, acNormal,,, acFormEdit ...
    (microsoft.public.access.formscoding)
  • RE: Form updates table when it should not
    ... have created an afterUpdate event which then runs a query with selected ... When I open the form, it has data from previous query. ... I tried to initialize in load event, ... DoCmd.OpenForm stDocName, acNormal,,, acFormEdit ...
    (microsoft.public.access.formscoding)
  • [QUIZ] Object Query Language for Ruby?
    ... Karmencita: an object query language for .NET ... // initialize the data source ...
    (comp.lang.ruby)
  • Re: [S390] cio: kernel stack overflow.
    ... Unless I'm missing something, it looks to ... me like this diff causes a change in the semantics of the code. ... I'm not sure how gcc ... I don't know whether gcc actually promises to initialize to ...
    (Linux-Kernel)