Totaling Date Difference in a Report 1

[mellay]

Hi! I have approximately 75 Forms within a database with 30 date difference fields in each Form which deal with employee vacancies over a year's period of time. The purpose is to determine the total days vacant within the reporting period, which is one year. Each Form will represent a different office within the state. All 75 Forms represent the entire state.

For example:
First Office
1st Employee vacant date 02-01-01, Vacancy filled date 05-14-01= 102 days vacant
2nd Employee vacant date 01-02-01, Vacancy filled date 03-14-01= 71 days vacant
Second Office
1st Employee vacant date 04-01-01, Vacancy filled date 07-14-01= 104 days vacant
2nd Employee vacant date 05-15-01, Vacancy filled date 08-01-01= 78 days vacant

I have successfully used the 'DateDiff' function to compute the number of days vacant for each of the 30 fields on each Form. I then get a total of the days vacant for all 30 fields on each individual Form by using a Text Box. =Nz([Text 302])+Nz([Text304])+Nz([Text 306]) etc, etc.

I need a Report that will total all Text Boxes on each of the 75 Forms for the total days vacant annually. (For example: 102+71+104+78, (taken from each Text Box), to equal a total of 265 days on the Report). Would it be possible to total the number of days vacant for each of the 75 Forms in an Annual Report by totaling the Text Boxes (from the Forms) on a Report? Every attempt to total the Text Boxes from each Form has failed me. I get a message asking for the parameter value of Text 302, then Text 304, etc . Any ideas or suggestions would be greatly appreciated.

Thanks!! mellay

 

[Zeroanarchy]

ok not to hard all you have to do in the report is as follows:
Goto report recordsourse click the ... button
this will take you into Sql code wich is very much the same as a query

In a new column add a new field:

Calculate:datediff[enter paramerters]
know from here you have two option
1. sum the total in SQL which then will prevent every record from being displayed on your report
To do this click the [totals] button and select sum in
calculate field.
2. Sum the total in a field on the report. =sum[calculate]

hope this help let me know if you have any other probs

zeroanarchy

 

[mellay]

Hi zeroanarchy and thanks for your prompt response! I am still having problems. I am a relatively new user and I am probably not following your instructions correctly. I continue to get a screen asking for the parameter values. Are the parameters the Text Box for each datediff? Ex: Text 302?

Under" Sum the totals in a field on the report. =sum[calculate]" , I am not sure that I am doing this correctly. Would you please expand your response? Please forgive my inexperience! Thank You!! mellay

 

[Zeroanarchy]

ok goto your report and click on the form properties.
you will notice in the record sourse the name of the table or query you are linking the form to.

Hint: Make sure it is a table or tables, basicily what you are about to do is create a SQL query within the report and it can do any or all your prior query stuff.

Ok you will notice a ... next to the record sourse click that and it will bring you into the SQL query. select all your fields that you want to be avaialbe in the report.
add this field at the end.

calculate: DateDiff([fielddate1]-[fielddate2])
ok close the query. "DO NOT CLICK THE SAVE BUTTON BEFORE CLICKING THE CLOSE BUTTON" on close it will ask if you want to save the SQL click yes.

Hint: If you click the save button it will create a query in you query folder, you do not need it, it will only confuse you later, better to hide it in the report that way it is protected and someone does not delete it because they do not know its purpose.

ok create a field call it datesum, in the control sourse type
=Sum[(calculate)]

Make sure the field is in the report footer other wise it will most likely not do what you want it to do.

If you want to have the value show up anywhere other than report add a new field and in the control sourse type

=[datesum]

this should do it.

good luck
Zero

 

[mellay]

Hi Zero!! Thank you so much for your help!! I really appreciate it! I am still working on this and I am making head way! Thanks again!! mellay

 

[Zeroanarchy]

The fun of programming, hope it works out well ;-)