You may define functions such as CalcWeeks() in a Module. If it is defined
with the Public declarative, it is then available to any form, query, report
or other module. I've improved the function a bit to make it more forgiving
if data is absent.
Your module might look like this:
' -------------------------------------------
Option Compare Database
Option Explicit
Public Function CalcWeeks(D_Start As Variant, D_End As Variant) As Variant
Dim int_Days As Integer
If IsNull(D_Start) Then
' Decide what to do here
' such as return Null if no begin date
CalcWeeks = Null
Exit Function
End If
' but no end date is permissable - assume 'thru today'
int_Days = DateDiff("d", D_Start, IIf(IsNull(D_End), Now(), D_End))
' Show 1 decimal place
CalcWeeks = Round(int_Days / 7, 1)
End Function
' -------------------------
You may have many functions and subroutines in a module.
Data type Variant is used so that Null is allowed as input or output.
You could thus use it to create a calculated column in the query that is
used to provide the record source for your subform. If your subform is
linked to a parent form via Master/Child relationship via, say, JobName and
DeptName, it will be restricted to just those pairs.
In the subform's footer you may define a text box with the data source of
=Sum([Weeks]) and it will total the calculated weeks. In the previous
message I showed a SQL statement - if you are using the query design view
(aka the "grid" view, you would do the same thing like this -
Weeks:CalcWeeks([StartDate],[EndDate])
where the things within the [ and ] are column names.
If you use a calculation as an item in a row source for a query, note that
you should not give the user the ability to type a value in - it won't let
you . Access will calculate it for you. So for caclulated fields you should
probably set these properties:
TabStop = False
Locked = True
Cosmetically you might set the background to transparent to give a visual
indication that it is a calculate field rather than an input field.
You don't have to do anything to have the calculation 'triggered' - change
either of the dates and the calculation will be performed.
A bit of an elaboration on using functions - in a query you are not
restricted to using just column names from the table(s) that you reference.
You may define a column on the fly using functions - either library functions
(there are dozens if not hundreds of them) as well as user-defined functions
such as CalcWeeks(). To define a column on the fly, provide a name followed
by a colon and then the function reference.
JudyB said:
Thank you so much for your quick response. This is my first attempt at a
database and I must say that it has been a real challenge. After reading
your response, I am feeling even more challenged. My table does not store
the WeeksService but, I do have a ServiceRecord Query that calculates the
WeeksService. I tried to use my query as my record source for the subform
and that showed all the information in the subform but, then I was not able
to enter any additional data. Where do I need to put your code?
--
JudyB
NKTower said:
Let me assume that your table looks like this.
JobName type text
DeptName type text
StartDate type date
EndDaet type date, null allowed
You should not store Weeks in the table, but rather calculate it on the fly,
with a function like this, Note that <space><underline> at the end of a line
is used to wrap long lines in VBA:
Public Function CalcWeeks(D_Start As Variant, _
D_End As Variant) As Single
Dim int_Days As Integer
int_Days = DateDiff("d", D_Start, _
IIf(IsNull(D_End), Now(), D_End))
' Show 1 decimal place
CalcWeeks = Round(int_Weeks / 7, 1)
End Function
Note that if an EndDate is not present (i.e. still on the job) it will
substitute today's date.
Then the record source for your subform might look like this:
SELECT tbl_Assignment.JobName,
tbl_Assignment.DeptName,
CalcWeeks([StartDate],[EndDate]) AS Weeks
FROM tbl_Assignment
GROUP BY tbl_Assignment.DeptName,
tbl_Assignment.DeptName;
Filter by JobName and/or DeptName as needed.
In your subform's footer you may then have a text box with source
=Sum([Weeks])
:
I have a subform which has fields - ServiceRecordID, DeptName, JobName,
DateIn, DateOut, and WeeksService. The WeeksService field automatically
calculates the difference between the DateIn and DateOut fields. I used the
code =Sum([Weeks Service]) to get the total of all weeks service for an
individual. All works fine. However, I would like to be able to sum only
the weeks service an individual may work in one department. 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. I would like to be able to sum only the weeks spent in the
Sales Department (104 Weeks). It it possible to calculate only weeks service
in any given department? I hope I have provided enough information. Any
help is appreciated in advance. Thank you!
