Counting Records

[Guest]

I have a report that tracks the files that are "in house" for porcessing.
The report shows all our clients whether we've received their files or not.
Once we receive a file, a date is entered into the database.
I'm using:
=Sum(IIf(IsNull([InProcess]),0,1)) to count the number of recods in a report
where there is a date entered. This worked great.

On the report, the date is showing up in the format: mm/dd/yyyy. For
readability fo the report, I wanted to reformat the date to show just the 2
digit year.

I went into the query and formatted the InprocessDate with:
InProcessDate: Format([Inprocess],"m/dd/yy"). The dates in the report look
perfect.

Now when I try to use the same logic to count the records with dates
eveterd, it counts ALL the records.

Does formatting the date in the manner above put a value in the query? Why
is it counting all the records and not jsut the ones with dates. How do I get
around this problem?

Thanks!

 

[Duane Hookom]

Don't format values in the query. Your query doesn't require formatted
values, your report does. Change the format property of the control that
displays the date.

 

[Marshall Barton]

I have a report that tracks the files that are "in house" for porcessing.
The report shows all our clients whether we've received their files or not.
Once we receive a file, a date is entered into the database.
I'm using:
=Sum(IIf(IsNull([InProcess]),0,1)) to count the number of recods in a report
where there is a date entered. This worked great.

On the report, the date is showing up in the format: mm/dd/yyyy. For
readability fo the report, I wanted to reformat the date to show just the 2
digit year.

I went into the query and formatted the InprocessDate with:
InProcessDate: Format([Inprocess],"m/dd/yy"). The dates in the report look
perfect.

Now when I try to use the same logic to count the records with dates
eveterd, it counts ALL the records.

Does formatting the date in the manner above put a value in the query? Why
is it counting all the records and not jsut the ones with dates. How do I get
around this problem?

Don't do this in the query. Just set the report's text
box's Format Property to mm/dd/yy