DCount and function examples

[Imbriani]

Can anyone point me toward a resource that give examples for the construction of DCount functions plus other functions? Help is not broad enough and I don't seem to find much online speaking to this.

kathy

 

[jebry]

Hi Kathy!

The Access help files seem to give a good accounting of the aggregate functions, at least the files that our IT dept has given me access to. In general the aggregate functions have the following form:

DCount("YourField", "YourTableorQuery", "YourCriteria")

All of the parameters must be in the form of a string, either a literal or a variable or some combination thereof. If you have a specific function that you want addressed let us know.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[RoyVidar]

You sure you've exhausted the help files? In VBE type DCount, then hit F1, type DLookup hit F1 (use the See Also links within the help files too).

Enter the search section of the site. Enter DCount (+ more keywords?), then search...

Use your favourite web search, enter +Access +DCount (+ more keywords?)...

Roy-Vidar

 

[Imbriani]

What you're cited from help, I've found, the DCount("YourField", "YourTableorQuery", "YourCriteria")
but, as luck would have it, I need more complex functions. For instance, I have a DCount function as follows:

=DCount("[resultelisa]","Main","[resultelisa] = 'Positive'")

I need to know if I can put another condition on the end to allow the user to select date parameters for this function to run. Something like:

=DCount("[resultelisa]","Main","[resultelisa] = 'Positive' AND
[dtelisa] BETWEEN {choose beginning date} AND {choose ending date}")

I know this is all wrong, but is something like this possible? This is used as a calculated field on a report that counts all the ELISA Positive samples between two dates.

 

[RoyVidar]

Help file states that the criteria is just a SQL where clause without the keyword Where. Yes you can continue adding more criteria to the domain aggregate function. I don't think you can have those kind of parameters. I don't know, because I really don't want to find out, cause such parameters are a real pain in the rear, cause there's no mean of validating it. Rather use form controls, then:

[tt]=DCount("[resultelisa]","Main","[resultelisa] = 'Positive' AND
[dtelisa] BETWEEN #" & format$(txtStart,"yyyy-mm-dd") & "# AND #" & format$(txtEnd,"yyyy-mm-dd") & "#")[/tt]

But wouldn't you rather consider opening a recordset in stead of using domain aggregates like this?

Roy-Vidar

 

[gol4]

here is a msdn article on dlookup. Usage is similar

http://support.microsoft.com/?kbid=208786

 

[jebry]

Hi!

Normally you would use square brackets:

[choose beginning date] AND [choose ending date]

but I don't know if that will work in an aggregate function. You could create a query for your domain and include this parameter in the where clause of the query.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Imbriani]

I tried basing the calculated control that contains this function on a parameter query, but I just couldn't make it work. The report itself is based on a query with some sumof controls and that seemed to complicate the issue. That's why I chose the DCount function, because I could call these totals from the main table without going through a query. At run time, the user of the report selects the inclusive dates he wants to pull data from. All other data is gotten in this manner, except for the counts from above. I found, though, if I tried to add addtional fields to the querys containing sum columns, I had problems. Make sense?