Report that never runs...

[btothap]

Dlookup in function, is there a way around?

Hiya.
First off i'm in access02, I have a big nasty query that pulls data from local tables as well as a remote table elsewhere on the lan. The remote table is in access97 and is generated by our phone system reporting software and can't be modified. When running the report(or query for that matter) with the phone system data included, it can take up to several hours to run depending on the time of the month. If I strip away the phone data, the report/query will run in under a minute. Recently I've had to add vb script to the report that performs dLookups on another table. With that function in place the report will not run, period. I've left it for 36 hours and it will sit idle with the status bar full of little blue dots.

I guess my question is really about optimization and why the insane slowness. I've tested the network and speed shouldn't be an issue there. I can run the reports from a third location, accessing both tables remotely, and find the same results. I limit the recordset in the phone table with a query, prior to the one that feeds the report, but it doesn't alter the horrendous run time. I know this is probably an impossible question to answer, but any help would be greatly appreciated.

Sorry for being so long winded.

-shane.

 

[btothap]

Hi Pat, thanks for your help.

I tried to find a way around using the DLookups, because as i've read in your posts before, they're slow, but in this application they seemed like the only route. Basically for each record I need to calculate based on totals from other records, for example if joe salesman is ranked #6 on his profits for the month, i need a calculation that will show the dollar amount necessary to move him up to #3. Here's the function i'm using.

Private Function gprank()

Dim rankadj
rankadj = Me.mugp_rank - 3

If Me.gpranknum = 1 Then
gprank = "Leading By " & Me.gpTtl - DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = 2")
ElseIf Me.gpranknum = 2 Then
gprank = DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = 1") - Me.gpTtl & " Moves you to #1"
ElseIf Me.gpranknum = 3 Then
gprank = DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = 1") - Me.gpTtl & " Moves you to #1"
Else
gprank = DLookup("[MU_GP]", "ranking_query", "[mugp_rank] = " & rankadj & "") - Me.gpTtl & " Moves you up 3 places"
End If

End Function

Where gpranknum is the individuals ranking
gpTtl is the profit for the individual

If I can do this without the DLookups i'd be very grateful if you could point me in the correct direction as to how since i have to calculate on records other than the current one.

Thanks again for your help.

-shane