function slow in query

[Grd]

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

 

[Robert Morley]

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

 

[John Spencer]

Well, first what does the function do? It may be possible to do what
you want right in the query.

IF not, can you modify the function so you pass in the reference to the
form control from the query.

Mod to your function
Function FiscalYear(TableDate, OtherDate)
....
End Function

Call from query

FiscalYear([NameofDateField],Forms!frmMyForm!txtBox)

The benefit of getting the value in the query, is that the query will
(probably) look up the value on the form one time on execution and then
keep the value for the entire query execution.

Again, if you post your function, there may be ways to improve its speed
or you may be able to do the entire thing in the query without a call to
the function.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Grd]

Hi John,

Thanks for the reply. My function is below (with comments). If the query
only looks it up once than that would be the key.

Any comments you may have on the function would be very interesting

Tx
Grd

Function CurrentFiscalYear(InputDate As Variant) As Boolean
' Variable Declarations
Dim DateOnForm As Date
' Variable Assignments
DateOnForm = Forms!frmChooseReport!txtReportingMonth
Select Case DateOnForm
Case Is >= DateSerial(Year(DateOnForm), 4, 1) 'we are past April
1st
If InputDate >= DateSerial(Year(DateOnForm), 4, 1) And
InputDate <= DateSerial(Year(DateOnForm) + 1, 3, 31) Then
CurrentFiscalYear = True
Else
CurrentFiscalYear = False
End If
Case Is <= DateSerial(Year(DateOnForm), 4, 1) 'we are before
April 1st
If InputDate >= DateSerial(Year(DateOnForm) - 1, 3, 31)
And InputDate <= DateSerial(Year(DateOnForm), 4, 1) Then
CurrentFiscalYear = True
Else
CurrentFiscalYear = False
End If
End Select
End Function

Well, first what does the function do? It may be possible to do what
you want right in the query.

IF not, can you modify the function so you pass in the reference to the
form control from the query.

Mod to your function
Function FiscalYear(TableDate, OtherDate)
....
End Function

Call from query

FiscalYear([NameofDateField],Forms!frmMyForm!txtBox)

The benefit of getting the value in the query, is that the query will
(probably) look up the value on the form one time on execution and then
keep the value for the entire query execution.

Again, if you post your function, there may be ways to improve its speed
or you may be able to do the entire thing in the query without a call to
the function.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

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

 

[Grd]

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