Queries runs for ever - Code and Formula

[NadSadBad]

Hi,

I have the following module with the following code in it. It basically is
used to calculate the difference between 2 date values and excludes weekends
(Saturday and Sunday):

Function DateDiffXWE(stDate As Date, fiDate As Date) As Integer
Dim nDays As Integer

nDays = 0
While stDate < fiDate
stDate = DateAdd("d", 1, stDate)
If Weekday(stDate) > 1 And Weekday(stDate) < 7 Then
nDays = nDays + 1
End If
Wend
DateDiffXWE = nDays
End Function


Now in a query, I have placed the following formula in one of the columns:

DateDiffXWE([tabActualExpenditure].[CreatedOn],[tabClientDetails].[TerminationDateUpdated]) and the criteria for it is <=10.

When I run the report that runs the query with the above formula, it takes
absolutely ages to run, in fact its just keeps running all the time and the
report I think will never appear, which I have been told is happening because
the code is running in a loop and that queries don’t like formulas like this.

Can someone please advise what I can do to reduce the time for this query to
run, is it wise to use this formula and what are my alternatives to reduce
time.

I desperately need help on this.

Thanks in advance

 

[Van T. Dinh]

The problem is that if the stDate and fiDate are, says, 3 years apart, the
While loop will be iterated more than 1000 times. If you have 100K Records,
you are looking at 100 million iterations of the While loop.

You may need to think of some other algo. either in the construction of the
Query or the function. For examples:

1. In the Query, calculate stDatePlus10WorkingDays and put the criteria
that the fiDate must be between stDate and stDatePlus10WorkingDays.

2. In the function, you may want to work out the number of whole weeks +
number of working days in the remaining partial "week". This should
requires a lot less iterations to get the number of working days (number of
whole weeks * 5 + working days in the remaining partial week).

--
HTH
Van T. Dinh
MVP (Access)

Hi,

I have the following module with the following code in it. It basically is
used to calculate the difference between 2 date values and excludes weekends
(Saturday and Sunday):

Function DateDiffXWE(stDate As Date, fiDate As Date) As Integer
Dim nDays As Integer

nDays = 0
While stDate < fiDate
stDate = DateAdd("d", 1, stDate)
If Weekday(stDate) > 1 And Weekday(stDate) < 7 Then
nDays = nDays + 1
End If
Wend
DateDiffXWE = nDays
End Function


Now in a query, I have placed the following formula in one of the columns:

DateDiffXWE([tabActualExpenditure].[CreatedOn],[tabClientDetails].[Terminati
onDateUpdated]) and the criteria for it is <=10.

 

[Gary Walter]

Hi,

It does not look (to me) like an infinite
loop problem, but I imagine has to do
with putting criteria on calculated field
over a large range of data.

Easy enough to test...
just remove criteria and run query.
Maybe better yet, assuming over 10 working
days that you would have max of 2
weekends...

Remove original criteria from query, then
add field that just determines datediff
between the 2 date fields. Put a criteria
of <=14 on this query field.

Turn this query into make table (for say "tblReport")
and run once.

Now change this query to append query
and save (say as "qryapptblReport").

When time to run report...

CurrentDB.Execute "DELETE * FROM tblReport", dbFailOnError
CurrentDB.Execute "qryapptblReport", dbFailOnError

open report based on query of tblReport
where you use original criteria of <=10
on calculated DateDiffXWE field in tblReport.

Of course...I could misunderstand...

good luck,

gary