counting date fields on forms (1 Viewer)

[Missyd]

I need to generate a report based on 5 different forms all containing 6 different date fields. All I need to know is how many have had dates inputted for each and a grand total. I know I need to use a counter but, I don't know the details on where to put this at. Please help.

 

[andyh]

Try using another field on the forms.

Use code for the on exit or on change for the date field to say me.[counterfield] ="1"

Then you can sum all the counter fields. make sure you call the counter fields a different name (counter1, counter2 e.t.c) to avoid confussion.

 

[Missyd]

Thanks alot. I will try this out and let you know what happens.

 

[Missyd]

This works but, when I close the form and open it back up it clears this number. Also, if I go in and delete dates it counts them as well. I put the me.counter in the on change event. Is there any way that I can do this and have it save the number. What about using the IsDate function? Any help is appreciated.

 

[Missyd]

Well, I take that back. Sorry. It only counts when all six date fields are either deleted or there is a date put in. If it is less, it does not count. I have a counter for each field and then an additional field adding the total. Any ideas?

 

[TexasTJ]

Five different forms??? Are these forms based on five separate tables?

Look into building a union query - possibly based on a query for each table where the information is grouped by date with a count.

Give more info on how things are structured (separate tables? Do you want to compare Date 1 from each table against each other or pool all six dates from each of the five forms?).

Good luck.

 

[Missyd]

Hi. Yes this is 5 forms based on 5 different tables. I don't need to know anything except for how many dates are put in for each category and a total of all. I don't need to know what the dates are or who the person is, just need a count. ex: Job Search Form - 6 date fields available for each Contact in the Contact table. Job Search Table holds the dates. I need a report on how many times all contacts were helped with a job search. Does this help?