Setting record with null field as current record

[JudyB]

I have a subform whick has fields: ServiceRecordID, DeptName, JobName,
DateIn, DateOut, and WeeksService. If the DateOut field is left null, the
field is programmed to use the current date. I would like to be able to
establish this record as the Current record and then total the weeks service
an individual may have worked in the Department showing for that record. For
example, an individual may spend 52 weeks in the Sales Department, move to
the Accounting Department and stay 40 weeks, and then move back to the Sales
Department for another 52 weeks to current date. I would like to be able to
sum only the weeks spent in the Sales Department (104 Weeks) and have the
results automatically calculate in a new field on the subform. Can anyone
help? Thanks in advance.

 

[Graham Mandeno]

Hi Judy

I'm not quite sure what you mean by "If the DateOut field is left null, the
field is programmed to use the current date."

I'm hoping that you are not storing WeeksService in your table. It does not
belong there - instead it should be a calculated field in your query:
WeeksService: DateDiff( "ww", [DateIn], Nz( [DateOut], Date() ) )

[For more information about using calculated fields, see this article:
http://office.microsoft.com/download/afile.aspx?AssetID=AM102816411033]

You can then have a calculated control on your subform txtTotalService:
=DSum( "WeeksService", "YourQueryName",
"EmployeeID=" & [EmployeeID] & " and "DeptID=" & [DeptID] )

[You may need to modify this expression to match your own field names and
data types]