Query - Aggregate

[Vsn]

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

 

[Duane Hookom]

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.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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>")

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRHIaZYechKqOuFEgEQJSigCdGNRVS0A9pqz0epHfbwfVZAGcdg0AnA3+
23GfJrKM+vSxGLqErKnvIG9f
=NQV6
-----END PGP SIGNATURE-----

 

[Michel Walsh]

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

 

[Vsn]

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