Query - Aggregate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vsn - 22 May 2006 20:45 GMT

Hi all,

Can someone help me and explane why below potions do result in a difftent
number?

Query:
SELECT tblPersonnel.ShortName, tblPersonnel.RPExpDT,
tblPersonnel.Designation
FROM tblPersonnel
WHERE (((tblPersonnel.RPExpDT)<Date()+45) AND
((tblPersonnel.fResignationDate) Is Null))
ORDER BY tblPersonnel.RPExpDT;

The query shows 18 records, which is the correct result.

Aggregate function:
=DCount("RPExpDT","tblPersonnel","RPExpDT < #" &
Format(Date()+45,"dd-mm-yyyy") & "# and isnull(fResignationDate)")

The Dcount fuction shows 5, which is faulty.

I have no idea, can someone give me a clue.

Furthermore I do not understand how to use below as explained on the MS
Access help:

Expression Description
RowCount:Count(*) Uses the Count function to count the number of records
in the query, including records with null (blank) fields.

If I use this in above shown query it results in an error; You tried to
execute a query that does not include the specified expression as part of an
aggregate function or grouping. (Error 3122)

Thx alot,
Ludovic

Reply to this Message

Duane Hookom - 22 May 2006 20:50 GMT

Try fix your date format:
=DCount("RPExpDT","tblPersonnel","RPExpDT < #" &
Format(Date()+45,"dd-mmm-yyyy") & "# and isnull(fResignationDate)")

DCount() returns a single value while the query may return many values.

Signature

Duane Hookom
MS Access MVP

> Hi all,
>
[quoted text clipped - 32 lines]
> Thx alot,
> Ludovic

Reply to this Message

Vsn - 24 May 2006 19:46 GMT

Duane,

Thx, for your solution it worked fine, i do think the trick was in the end,
the ISNULL function.

All others thx for responding.

Regards,
Ludovic

> Try fix your date format:
> =DCount("RPExpDT","tblPersonnel","RPExpDT < #" &
[quoted text clipped - 37 lines]
>> Thx alot,
>> Ludovic

Reply to this Message

MGFoster - 22 May 2006 21:08 GMT

Set it up this way (all one line):

=DCount("RPExpDT","tblPersonnel","RPExpDT < Date()+45 AND
fResignationDate IS NULL")

Reason: The criteria portion of the DCount() function should look
exactly like the WHERE clause in an SQL command.

You can use the asterisk in the DCount function like this:

DCount("*","<table name>","<criteria>")

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> Hi all,
>
[quoted text clipped - 29 lines]
> execute a query that does not include the specified expression as part of an
> aggregate function or grouping. (Error 3122)

Reply to this Message

Michel Walsh - 24 May 2006 12:00 GMT

Hi,

also, try

=DCount("*","tblPersonnel","RPExpDT < #" &
Format(Date()+45,"dd-mm-yyyy") & "# and isnull(fResignationDate)")

if your field RPExpDT has 3 nulls, your first expression was not "counting"
them. Using * instead of a field name count the records, while using a field
name (or an expression) count the records having a not-null value for that
field/expression.

Hoping it may help,
Vanderghast, Access MVP

> Hi all,
>
[quoted text clipped - 32 lines]
> Thx alot,
> Ludovic

Reply to this Message