I want to avoid using DSUM in this form. Dont know how!

[dudezzz]

I have a form based on a lengthy SQL query.

I have several controls in this form whose control Source has the following formula:

=Nz(DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And [projectid]=" & "'" & [projectid] & "'"),0)


When I navigate from one record to another record in this form, the values in the controls take a looooong time to populate (most of the time they come up as blank and when I move my mouse over that control - the value gets populated....this is slow !!!).

I believe this is happenning because I am using DSUM function all over the form.

What is the fastest way to display this control WITHOUT using DSUM?

Many thanks!

 

[ScottGem]

I have a form based on a lengthy SQL query.

I have several controls in this form whose control Source has the following formula:

=Nz(DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And [projectid]=" & "'" & [projectid] & "'"),0)


When I navigate from one record to another record in this form, the values in the controls take a looooong time to populate (most of the time they come up as blank and when I move my mouse over that control - the value gets populated....this is slow !!!).

I believe this is happenning because I am using DSUM function all over the form.

What is the fastest way to display this control WITHOUT using DSUM?

Many thanks!

Create a query that groups by projectID and quarter and sums [Capital], Then use DLookups to get the sum from the query. Should be bit faster.

 

[dudezzz]

Is there a solution without using Domain function?

Thanks for the suggestion.

If its possible, I would like to avoid using Domain functions altogether.

Any other solutions apart from using dlookup / dsum please?

 

[ScottGem]

Thanks for the suggestion.

If its possible, I would like to avoid using Domain functions altogether.

Any other solutions apart from using dlookup / dsum please?

None I know of. The only way to get a value from a query is through a Domain aggregate. The key to using them is to run then against as samll a recordset as you can.

The only other possibility is to loop thru each record summing the field in a custom function.