if DCount works to get count, what simple function call can I use to get ...??

rdjohnso

Registered User.
Local time
Yesterday, 17:36
Joined
Feb 10, 2006
Messages
17
Got this from someone else to tell me how to get a count of a group in a certain period of time.... NOW

=DCount("*","tbl_Person","Now() Between StartDate And EndDate And GroupType='GWHIS'")

*******************
if DCount works to get count, what simple function call can I use to get a record cell from a table...

For instance.... typically I could write SQL to get this:

SELECT ForecastAmount FROM rtbl_Forecast WHERE ForecastTitle = 'Software Licenses' AND ForecastTimePeriod = 3/1/2006

It would Return: 3000

.....I dont know Access well enough to know what funct call I can use... i figure there has to be something like above I can use....

Please advise, thanks,

Ron
 
*** Further Clarification... I am trying to avoid having to call functions... just want something like the DCount that I can put in the ControlSource for each cell needed in a report....
 
someone else offered this suggestion....

=DLookup("[ForecastAmount]", "rtbl_Forecast", "[ForecastTitle] = Software Licences AND [ForecastTimePeriod] = 3/1/2006")

would something like this not work? it gives me an #Error right now....

***************************************************
This is my actual Function.... needs some syntax help though.....

=DLookUp("[ForecastAmount]","rtbl_Forecast","[ForecastTitle] = Software Licenses AND Month([ForecastTimePeriod]) = '3' AND Year([ForecastTimePeriod]) = Year(#Now()#)")
***************************************************


rdjohnso said:
*** Further Clarification... I am trying to avoid having to call functions... just want something like the DCount that I can put in the ControlSource for each cell needed in a report....
 
=DCount("*","tbl_Person","Now() Between StartDate And EndDate And GroupType='GWHIS'") - I believe I told you in an earlier post that this statement is incorrect. You need to use Date() rather than Now(). If StartDate = 2/1/2006 and EndDate = 2/21/2006 then 2/21/2006 09:21 PM will NOT be within the range. That is why you don't use Now() when you really want ONLY a date!

=DLookUp("[ForecastAmount]","rtbl_Forecast","[ForecastTitle] = Software Licenses AND Month([ForecastTimePeriod]) = '3' AND Year([ForecastTimePeriod]) = Year(#Now()#)") - you seem obsessed with Now() but that's not all that's wrong with this code.

=DLookUp("[ForecastAmount]","rtbl_Forecast","[ForecastTitle] = 'Software Licenses' AND Month([ForecastTimePeriod]) = 3 AND Year([ForecastTimePeriod]) = Year(Date())")

I hope you have plenty of time to wait for this report to run. Populating its controls by using DLookup() will take a long time. You are far better off with a report that is bound to a query that selects only the period you want.
 
Last edited:
Thanks for the response Pat.... I understand and will use the Date(), makes sense..... Forgive me for my scrap at this.. still new at alot of these other functions.

Thx

Pat Hartman said:
=DCount("*","tbl_Person","Now() Between StartDate And EndDate And GroupType='GWHIS'") - I believe I told you in an earlier post that this statement is incorrect. You need to use Date() rather than Now(). If StartDate = 2/1/2006 and EndDate = 2/21/2006 then 2/21/2006 09:21 PM will NOT be within the range. That is why you don't use Now() when you really want ONLY a date!

=DLookUp("[ForecastAmount]","rtbl_Forecast","[ForecastTitle] = Software Licenses AND Month([ForecastTimePeriod]) = '3' AND Year([ForecastTimePeriod]) = Year(#Now()#)") - you seem obsessed with Now() but that's not all that's wrong with this code.

=DLookUp("[ForecastAmount]","rtbl_Forecast","[ForecastTitle] = 'Software Licenses' AND Month([ForecastTimePeriod]) = 3 AND Year([ForecastTimePeriod]) = Year(Date())")

I hope you have plenty of time to wait for this report to run. Populating its controls by using DLookup() will take a long time. You are far better off with a report that is bound to a query that selects only the period you want.
 

Users who are viewing this thread

Back
Top Bottom