Displaying number of data occurances in query / table 2

[Moss100]

Hello, I have searched for some time, but can's see how or if I can achieve this.

I have a table called NAMES

The table has unique ID field called NAME_ID

It also has a field START (which is a date field)
and a field called RENEWAL (which is again a date field)

On my form called DETAILS I would like to show the number of records which have the start date and renewal date before today

So for example the form would state something like:

Records with start date before today = 30
Records with renewal date before today = 9

Thank you Mark

 

[dhookom]

You could add text boxes to the form header or footer with control sources of:

=Sum(Abs([START]<Date()))
=Sum(Abs([RENEWAL]<Date()))

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

That works great !

I also have a field called START_REMINDER

If I wanted to combine this with the code

=Sum(Abs([START]<Date()))

how would I do this?

For example I would like to count the instances of the START date as above, but only those where the field START_REMINDER is True

Many thanks Mark

 

[Moss100]

Its OK -

The code below works -thanks for all your help

=Sum(Abs([START]<Date()) AND (Start_Reminder = yes))

 

[Moss100]

Scrap that - it did not work - HELP!!

 

[PHV]

=Sum(Abs([START]<Date() AND Start_Reminder=True))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Perfect - Thank you

 

[Moss100]

OK all works fine BUT...

I was hoping to be able to make the detail of the form invisible as it is only the result numbers I am interested in.

Unfortunately when I set the visible property of the detail to No, the results do not get calculated.

Is there a work around for this or another way I can approach this from?

In essence I just want a form (which I will make a subform) to show how many overdue dates there are for certain tasks.

Many thanks Mark

 

[dhookom]

Build a totals query and use it as the record source of a subform that you can place in your form.

Duane
Hook'D on Access
MS Access MVP