Counting items in a report

[Guest]

I am using Access 2003

I am running a report from a query and I want to make a summary at the end.

I have two types of data , amounts and invoice reference numbers. These are
analysed by supplier for each detail line. There is not a reference for each
supplier on every detail line.

The invoice reference number is a text field.

My problem is with the reference numbers. I want to count the number of
invoice reference numbers for each supplier.

I have tried Count and DCount but cannot get either to work. In DCount I
think my problem is in setting the criteria. I have tried "<>"" " and " <>
NULL " and neither seem to work for me.

I have also noticed that when looking up DCount in the help file the example
shown puts quotation marks around each of the 3 elements. However if you use
the wizard to create the expression it does not use any quotation marks. So I
am not sure of the correct way to set up the expression.

I would be grateful if anybody could give me some help or guidance on
getting this to work.

Many thanks,

Sean Bishop

 

[Arvin Meyer [MVP]]

If you use Group Headers in your report such that each supplier is separated
by an entry in a group header and group footer, you can add an expression to
the controlsource of a text box in the group footer:

=Count([invoice reference number])

 

[Guest]

Mr Meyer,

Thank you for your response to my question.

I tried your suggestion but unfortunately I had omitted the fact that the
report was sorted and grouped by branches for which suppliers give a separate
account number., and your suggestion did not work.

However, I have carried on experimenting and have got the DCount formula to
work.
Firstly, I went back to the source table and not the query
Secondly, I altered the criteria from <>"" (double quotes) to <>'' (single
quotes)

This worked and I have been able to verify the accuracy of the results.

I am still confused however, as I used the wizard to set up the expression
and it did not work, I then went back to the help file and following the
example I enclosed the three elements of the expression in double quotes and
it worked. This has dented my confidence in the wizard.

Finally may I say, as a new user, it is very reassuring to know that when
you have a problem there are people willing to help you, so thank you again.