Show zero count as 0

[Sam]

How do I type the criteria in a Where total column in a
query if I want it to show records with a zero count also?

example:
column one column two column three
"Date" "AvailDate" "AvailDate"
Group By count where
<[Days]

If "date" is prior to any AvailDate I need the count to be
0.

Thanks,
Sam

 

[MGFoster]

How do I type the criteria in a Where total column in a
query if I want it to show records with a zero count also?

example:
column one column two column three
"Date" "AvailDate" "AvailDate"
Group By count where
<[Days]

If "date" is prior to any AvailDate I need the count to be
0.

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

Try:

SELECT [Date], Sum(IIf([Date]<[AvailDate],0,1)) As AfterDateCount
FROM TableName
WHERE [AvailDate] < [Days]

Assuming [Days] is a parameter.

Any record where the Date is before the AvailDate will not be counted;
all others will be counted.

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

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

iQA/AwUBQGEKqIechKqOuFEgEQIgvwCgidsRbJ0YovHEZ0rmJohzSLd5jzcAoLqT
uGkBcbLr9tVi+jPU3rIsNHrz
=bAUw
-----END PGP SIGNATURE-----

 

[Sam]

The records aren't being counted like you said. The
problem is that I need that earlier [date] to show in my
query with a zero count. Currently, the earlier [date]
isn't showing at all because of the zero count.

-----Original Message-----

How do I type the criteria in a Where total column in a
query if I want it to show records with a zero count also?

example:
column one column two column three
"Date" "AvailDate" "AvailDate"
Group By count where
<[Days]

If "date" is prior to any AvailDate I need the count to be
0.

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

Try:

SELECT [Date], Sum(IIf([Date]<[AvailDate],0,1)) As AfterDateCount
FROM TableName
WHERE [AvailDate] < [Days]

Assuming [Days] is a parameter.

Any record where the Date is before the AvailDate will not be counted;
all others will be counted.

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

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

iQA/AwUBQGEKqIechKqOuFEgEQIgvwCgidsRbJ0YovHEZ0rmJohzSLd5jz cAoLqT
uGkBcbLr9tVi+jPU3rIsNHrz
=bAUw
-----END PGP SIGNATURE-----

.