DCount slow

[D Willett]

Hi
I'm using DCount in several unbound text box's on my form.
Each DCount has its own query to pull data from resulting in the result being slow and sluggish. The for will load but it can take 15 - 30 seconds to show the counted value.
Here's what I'm using:

Date Field:(txtLastMon):
=Date()-Weekday(Date(),2)-7*(Weekday(Date(),2)<1)+1

Query (qryInProgressLastMon):
SELECT DBA_JOB_CONTROL_VIEW.JobID, IIf(Not IsNull([CompletionDate]),DateValue([CompletionDate])) AS Completed
FROM DBA_JOB_CONTROL_VIEW
WHERE (((IIf(Not IsNull([CompletionDate]),DateValue([CompletionDate])))=[forms]![frmWIP]![txtLastMon]));

Unbound Text:
=DCount("*","qryInProgressLastMon")

I'm wondering if there is a quicker way?

[HansV]

If you only use qryInProgressLastMon for this, change its SQL to

SELECT Count(*) AS C
FROM DBA_JOB_CONTROL_VIEW
WHERE (((IIf(Not IsNull([CompletionDate]),DateValue([CompletionDate])))=Date()-Weekday(Date(),2)-7*(Weekday(Date(),2)<1)+1));

Otherwise create a new query with this SQL.
Change the Control Source of the text box to

=DLookup("C","qryInProgressLastMon")

(use the name of the new query if necessary). Is that faster?

[D Willett]

I'll give it a try Hans. I'm sure there will be a big improvement.

Thanks again.

Kind Regards