Text box with calculations

[Ron_dK]

I have a form in my Dbase which has quite a number of text boxes on it.
The control source of the majority of these textboxes is something like :

=DCount(" [Survey-Input-ID]","[Qry-analyze-input]","[2] like 'S' and [Survey-conducted] between #4/1/2004# and #4/30/2004# and [23] like 'D' ")

When I open this form , it takes ages to see all the calculated values in the appropriate textbox.

Is there a way to speed this up, or maybe using VB for these functions.
Any help is greatly appreciated.

Cheers , Ron

 

[Mile-O]

I have a form in my Dbase which has quite a number of text boxes on it.
The control source of the majority of these textboxes is something like :

=DCount(" [Survey-Input-ID]","[Qry-analyze-input]","[2] like 'S' and [Survey-conducted] between #4/1/2004# and #4/30/2004# and [23] like 'D' ")

When I open this form , it takes ages to see all the calculated values in the appropriate textbox.

Well, to begin with, the domain aggregate functions (DCount, DSum, DLookup, etc.) are well known for their snail like properties.

The first question I'd be asking, from a design point of view, is why do I have so many textboxes?

Is it a design issue? The fact that you've got fields in the query called 2 and 23 would make me think that there's fields 1 through 23. Maybe even beyond. What's the purpose of this repeating group? Crosstab query?

Also, the Like evaluation won't work without a wildcard (either * or ?) as you are offering a literal criteria with it. [2] = 'S'

 

[Ron_dK]

Mile,

You're right, I have some 26 fields in the initial table. For some reasons I would not like to change this.
On the form the textboxes will give me figures on certain activities
conducted in a certain period.
Allthough using this many textboxes with the underlaying calulations is not a very elegeant method, it works and the output is consistent.
The only thing is that it takes so long to get all fields filled on the screen with the relevant figure ( calculation time that is..)
So , no VBA alternative .......?

Cheers, Ron

 

[Pat Hartman]

One alternative is to replace the "likes" with =. The relational operator Like forces the database manager to do a full table scan whether the rest of the criteria required it or not. You can also index your [Survey-conducted] field.

 

[Ron_dK]

Pat,

Changing the "likes" to = did not give any acceleration, but indexing
the Survey-conducted field really did the trick.
I have opened and closed the form several times and the figures in the textboxes are really fast populated.

Thanks for your worthy advice.

Ron

 

[Pat Hartman]

No, in and of itself changing the likes to equals will not speed up the queries but now that you have added the index on date, if you go back to using like, the queries will slow to a crawl again because Jet won't be able to make use of the new index.