You don't really want to store the value for Number of Open Days in your
table. That value can always be calculated from the other two fields (Open
Date and Date Completed) via a query, and you can display the results of
that calculation on the form.
So, eliminate the Number of Open Days field from your table.
There are two ways to do the calculation -- one involves adding a calculated
field to the query that you're using as the form's RecordSource; the other
involves using an expression in a textbox on the form. My preference is to
do it in a query.
First, put the following function (taken from
http://www.tek-tips.com/faqs.cfm?fid=3928) into a regular module in your
database (be sure to name the module something other than DateDiff2; perhaps
name the module basCode):
' ** START OF CODE
Public Function DateDiff2(ByVal d1 As Date, ByVal d2 As Date) As String
Dim d3 As Date, nDays As Integer
Dim d1Day As Integer, nDayStart As Integer
Dim nYears As Integer, d1Mon As Integer, MonLast As Integer, nn As Integer,
nMon As Integer, d1Year As Integer
If d1 > d2 Then
d3 = d1
d1 = d2
d2 = d3
End If
d1Day = Day(d1)
d1Mon = Month(d1)
d1Year = Year(d1)
nn = 0
nMon = 0
nDays = 0
nYears = 0
MonLast = d1Mon
Do While d2 > d1
d1 = d1 + 1
nn = nn + 1
nDays = nDays + 1
If (Day(d1) = d1Day) And Month(d1) <> MonLast Then
nMon = nMon + 1
nDays = 0
MonLast = Month(d1)
If nMon = 12 Then
nMon = 0
nYears = nYears + 1
End If
End If
Loop
DateDiff2 = "Years: " & Format(nYears, "00") & " Months: " & Format(nMon,
"00") & " Days: " & Format(nDays, "00")
End Function
' ** END OF CODE
Assuming that you are using a query as the form's RecordSource, go to that
query and open it in design view (NOTE: if you're using the table name as
the form's RecordSource, then create a query that is based on that table,
and continue with what I'm suggesting here).
In the first empty column, put the following expression in the Field: box:
[Number of Open Days]: IIf([Open Date] Is Null, Null, IIf([Date Completed]
Is Null, DateDiff2([Open Date], Date()), DateDiff2([Date Completed], [Open
Date]))
Save and close the query.
Then, on your form, put [Number of Open Days] in the ControlSource property
of the textbox that is to display the "Number of Open Days".
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Palacelaw said:
In Excel:
C2 = Open Date
L2 = Date Completed
M2 = Number of Open Days (expressed in Y, M, D format)
M2 calculates a value based on the values in C2 and L2. If C2 is blank
then
M2 is blank. If C2 has a value then the formula looks to the contents of
L2
to calculate a value for M2. If L2 is blank then M2 populates with the
difference (in Y, M, D format) between today's date and the value in C2.
If
L2 has a value then M2 populates with the difference (in Y, M, D format)
between C2 and L2.
In Access:
I created a table that has among other things fields called Open Date,
Date
Completed, and Number of Open Days. I then created a form based on this
table. In the form once the user puts a value in the Open Date field I
want
Number of Open Days to automatically populate value based on the same
logic I
used in Excel.
Does that make sense? Thanks again for your help. I really appreciate
it.
