Compare number of records read vs number returned access

[Greg]

I need to report the number of records matching a selection query vs the
number that don't.

eg: 'There are 24 records out of 456 that match your criteria'

 

[John Spencer]

In the report footer add a text control.

Set the control source to
= "There are " & count(*) & " records out of " &
DCount("*","YourTableName") & " that match your criteria"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

Match: Sum(IIF([TEST], 1, 0)) Not_Match: Sum(IIF([TEST], 0, 1))

 

[Greg]

John - thanks very much, one final tweak is required, In 2003 Access DCount
has a third parameter, criteria. My attempts to get the total record count
to be restricted to a date range on the tdate field of my table have only
produced a rather unhelpful - 'error' symbol.

I need to restrict the sum of the total records to the same date range as
the report. My construction is
Dcount("*","Tablename","tablename.tdate>"2008-12-31" and
tablename.tdate<"2009-02-01") to capture all the January only records.

Thanks so much for your assistance.

 

[John Spencer (MVP)]

You were close. Dates should be delimited by # characters instead of "
characters in Access. So try the following expression.

Dcount("*","Tablename","tdate>#2008-12-31# and tdate<#2009-02-01#")

The trick will be to make those dates dynamic. That might be possible
depending on your reports source. Or perhaps you can use this little trick if
you are displaying the date in the report.

Dcount("*","Tablename","tdate>=" & Format(Min(TDate),"\#yyyy-mm-dd\#") & " and
tdate<= " & Format(Max(TDate),"\#yyyy-mm-dd\#"))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Greg]

Perfect !!! - Thanks !!!

You were close. Dates should be delimited by # characters instead of "
characters in Access. So try the following expression.

Dcount("*","Tablename","tdate>#2008-12-31# and tdate<#2009-02-01#")

The trick will be to make those dates dynamic. That might be possible
depending on your reports source. Or perhaps you can use this little trick if
you are displaying the date in the report.

Dcount("*","Tablename","tdate>=" & Format(Min(TDate),"\#yyyy-mm-dd\#") & " and
tdate<= " & Format(Max(TDate),"\#yyyy-mm-dd\#"))



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County