How to count clients for a certain date??

[AnnaZ]

I have a database with a list of clients, their enter and exit dates. I need to do a query that will count how many clients we have on a specific date. Keeping in mind that clients leave. I can do a query that will prompt user to enter a date. However, how would I count the total number of clients on that date? thanks a lot for any help!

 

[ScottGem]

I have a database with a list of clients, their enter and exit dates. I need to do a query that will count how many clients we have on a specific date. Keeping in mind that clients leave. I can do a query that will prompt user to enter a date. However, how would I count the total number of clients on that date? thanks a lot for any help!

Create a form to enter the Date. Create a query with the client ID field and the enter & exit dates. In the criteria for the enter date use:

<Forms!formname!controlname

For exit date use:

>Forms!formname!controlname

where formname is the name of the form and controlname the name of the control where you are entering the date.

Now if you just want a count you could put a text box on the form and in the After Update event fo the date box put:

Me!txtCount = DCount('[keyfield]',"queryname")

Where keyfield is the name of your CleintID field and query name the name of the query where you filter for the date.

 

[AnnaZ]

Thanks Scott! I have a question about the form. Should I create a new table that will hold the date and the count fields for the form?

 

[ScottGem]

No. The form will be unbound. You can get the numbers by calculation just by using that form.