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

[rdjohnso]

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

 

[Zion7]

DCount(), is one of a few similiar "Aggregate Functions"


Dim intForecastAmt As Integer

intForecastAmt = DLookUp("ForecastAmount", _
"rtbl_Forecast", "ForecastTitle = 'Software Licenses' AND ForecastTimePeriod = #3/1/2006#")

Amongst Others,
DMax(), DMin(), DLast(), DFirst(), DSum(), DAvg(), etc..

 

[rdjohnso]

Zion... Thx but 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....

Maybe I should have specified this before...

Does this make sense?

R

 

[Zion7]

Not really because DCount, is a function.
It's all part of MS aggregate Functions?
You can use any of the functions I showed, and put in any control source, of an unbound TextBox.
You can even create your own functions, and do the same.

 

[rdjohnso]

someone else offered this suggestion....

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

would something like this not work?

 

[Zion7]

Yes & No.
For a control source yes. It's basically identical to mine.
I populate a variable, that populates a TextBox.
BUT, You need qualifiers around the date, like mine,"#".

 

[rdjohnso]

***************************************************
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()#)")
***************************************************

 

[PHV]

A lot of syntax errors here ...
You may try this instead:
=DLookUp("ForecastAmount","rtbl_Forecast","ForecastTitle=[!]'[/!]Software Licenses[!]'[/!] AND Month(ForecastTimePeriod]=[!]3[/!] AND Year[ForecastTimePeriod]=Year([!]Now()[/!])")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886