Speed

[NotGood@All]

I have a form that shows daily output. I get this information using this
code, =DCount("DateCreated","qryForStatsPageOnly","DateDiff('d', DateCreated,
Now)=0 ")
I use it 45 times to get the last 45 days by changing =0. It is getting
slower and slower the more records that are input, (200,000). Is there a
better way to do this or is this as good as it gets?

Thanks
NotGood@All

 

[John W. Vinson]

I have a form that shows daily output. I get this information using this
code, =DCount("DateCreated","qryForStatsPageOnly","DateDiff('d', DateCreated,
Now)=0 ")
I use it 45 times to get the last 45 days by changing =0. It is getting
slower and slower the more records that are input, (200,000). Is there a
better way to do this or is this as good as it gets?

Thanks
NotGood@All

Well, the domain functions can be pretty poky, and calling DCount 45 times is
NOT a good way to go - I sure hope you're not manually editing it for each
row!!

If you want the count of records for each of the last 45 days you can get it
in one query:

SELECT DateValue(DateCreated), Count(*) AS Output
FROM qryForStatsPageOnly
WHERE DateCreated >= DateAdd("d", -45, Date())
GROUP BY DateValue(DateCreated);

Note that Now() does NOT return today's date - it returns the date and time
accurate to the second. If DateCreated contains a time portion (i.e. it's
being set to Now() as a default or in your code), the above will work; if it
is just a pure date, created using Date(), then you can leave out the
DateValue() function call above. Be sure that DateCreated has an Index in the
table design either way in order to speed up your query.