Total Based on Calculation

[andysgirl8800]

Good Day!
I seem to be running into a calculation problem in one of my queries. I have two fields in the query, AdmitDate and DischargeDate. I built in an expression, Exp100 to catculate the date diff in days between these two dates. Now what I want to do is display a count of the grand total days. So, if I have the following three records:

ID AdmitDate DischargeDate Exp100
1..... 01/01/06.........01/15/06............15
2......07/12/06.........07/19/06............7
3......04/27/06.........04/30/06............3

My query should display:
Healthy Plan Total Patients=3
Healthy Plan Total # Days = 25

How can I program this to correctly display this information? Thanks for any help or advice, it would be most welcome at this point. Cheers.

 

[Brianwarnock]

You need a 2nd totals query to Count ID and Sum Exp100, add the text details as a field caption.

Brian

 

[andysgirl8800]

Thank you for your suggestion. Got it all working. Only problem now is that I have 8 different subforms in my parent form. Now I have to enter a date range 8 times to display the correct results. I'm uncomfortable with the margin of error available with so much repetitive entry. The date range will be the same for all 8 subforms. Is there any way to only enter the range once for all 8 subforms?

 

[Brianwarnock]

There must be but I'm not good with subforms, suggest you post it as a question in the Forms forum.
Best of luck

Brian

 

[Brianwarnock]

I see from a quick scan of the forum that you have asked this question, where is your date range coming from, how are the queries/forms fired off, if you had a original form in which you entered the date range and then started the process with a command button from that form surely all of the sub forms could access that original form for the criteria, or am I off course or talking rubbish?

Brian

 

[andysgirl8800]

Yes, I'm sorry, I have posted this in the Forms forum, but am not getting a reply as of yet. My date range is coming from each of the 8 queries, which are all built on the same table, but with different criteria. There are 8 subforms each has one of the 8 queries as a record source. It's complicated, from my point of view, but it IS displaying the information that I need. However, it is terribly inconvenient to have to enter the same date range for each of the 8 subforms in order for the parent form to open completely, not to mention that the data will be skewed if a user mistypes in any one of the 16 VB boxes (8 start dates and 8 end dates). I don't know if this is a form/button issue, or a query issue. I'm hoping someone else may have needed to use the same date range for multiple queries and has developed a solution. Thanks for your help.

 

[Brianwarnock]

I have used the same date range on multiple queries for multiple reports using a Daterange form and command buttons or a macro to open all the reports, I don't know the implications of wanting to open a main form and multiple sub forms, but would have thought that it was possible, just can't advise on the method.

brian

 

[andysgirl8800]

Ok, may I have some more details on how you did a date range form with a macro or button for your reports? I think I may give this a try. Thanks.

 

[The_Doc_Man]

It is possible to turn on FILTERING in the sub-forms and supply the criteria to each sub-form if the date fields in question are in the parent form. Look up Help on form filtering. It should be possible in VBA to update the form's properties when you select the sub-form to be displayed. The Filtering property is an overall property of the form as a whole, not a property of any control on the form.

The way I would approach this is to be sure the sub-forms were controlled by a button and that as a starting point, NO sub-forms were selected. Then if you click a particular form's "bring up the form" button, you can add some VBA code to muck the form's filter properties. NOTE that if you take this approach, you cannot apply TWO filters to the same form. So your queries must fully self-contained. (As opposed to having generic queries and applying different filters to the individual forms.)

 

[Brianwarnock]

The_Doc_Man who is more knowledgable than I may have solved your problem but what I was going to Post is this:

I don’t know your level of knowledge so I apologise in advance if the level is not correct.

Create an unbound form let’s call it frmDates , add two text boxes with date format called startdate and enddate, and a command button I use the command button wizard to do the rest (info in help). In your case I suppose it will open the main form.

The information typed into the frmDates controls are available to any Access objects by Forms!frmname!controlname thus your queries will use Forms!frmDate!startdate etc.

I hope this helps

Brian

 

[andysgirl8800]

Then if you click a particular form's "bring up the form" button, you can add some VBA code to muck the form's filter properties.

Doesn't this mean I still have to press a button each time for each subform? So, pressing 8 buttons?