The option group is not bound to either of the two date/time columns; its
could be bound to another column with possible values 1 or 2 to indicate if
an issue is active or closed, but that's not actually necessary as an issue
is active if DateOpened is not Null and DateClosed is Null; its closed if
DateClosed is not Null, so the option group could in fact be unbound, and is
better so as this eliminates the redundancy, and possible inconsistent data
which might result from this.
You can't bind check boxes to columns of date/time data type (well, you can
but as you've found out the results are pretty weird). The reason you are
getting the bizarre dates is that a check box has values of True or False,
which in Access are implemented as -1 or 0. Date/time data in Access is
implemented as a 64 bit floating point number with the integer part
representing the days and the fractional part the times of day. Day zero in
Access is 30 December 1899, so when the check box is False (0) the column to
which its bound will be that date, when its True (-1) the column's value will
be one day before, i.e. 29 December 1899.
So here's what to do. Remove the two check boxes and reinstate the option
group as an unbound control, setting it up to show no default value. In its
AfterUpdate event procedure put the code I sent you. Keep the unbound text
box with the expression I sent you as its ControlSource.
The function I sent you can be put either in the form's module or in a
standard module, in either case exactly as I sent you. I'd suggest the
latter as it can then also be used elsewhere in the database, e.g. in a
report or in a query. With the latter make sure you save the module under a
different name from that of the function, e.g. mdlDateStuff.
To get the option group to show the correct selected option for a particular
record on the basis of the date/time columns' values as you navigate to an
existing record put code like this in the form's Current event procedure:
' set option group to show no selection by default
Me.YourOptionGroup = Null
' set option group to show relevant selection
' on basis of dates opened and/or closed
If Not IsNull(Me.DateOpened) And IsNull(Me.DateClosed) Then
Me.YourOptionGroup = 1
Else
If Not IsNull(Me.DateClosed) Then
Me.YourOptionGroup = 2
End If
End If
Ken Sheridan
Stafford, England
blake7 said:
Hi Ken, I have done the following
1) created the two columns as suggested in my table.
2) The option group i created only allows you to save the selection into one
column with the default value of 1 or 2 ??
3) Instead I created two check boxes and set the control source to the two
new columns in the table, I set the 'on click' default to =Date(), but upon
clicking the 'date Open' check box it puts the date of 29th December 1899
(yes 1899) ???? into the field on the form. The date closed check box
displays 30th Dec 1899 when clicked?
4) I set up an unbound text box on screen and set the control source as
suggested, it displays 0
5) I Input the function in the form ok.
(nothing here above the function statement, ie private sub etc)
Function DaysOpen(varOpen, varClosed)
DaysOpen = DateDiff("d", Nz(varOpen, VBA.Date), Nz(varClosed, VBA.Date))
End Function
any ideas?
Ken Sheridan said:
Add two date/time columns, DateOpened and DateClosed say, to the underlying
table, one to record when an issue is opened, the other when its closed. In
the AfterUpdate event procedure of the option group set the value of the
relevant column, depending on which option is selected, to the current date
by means of the Date() function.
You can also build in some validation here. If the DateOpen column is Null
the selecting 'closed' would be inappropriate, so you can make make the
insertion of the current date in DateClosed conditional on
IsNull(DateClosed). Similarly the insertion of the date in DateClosed can be
conditional on Not IsNull(DateOpen). In each case a message box can be
popped up warning the user if an invalid selection is made, e.g.
Select Case YourOptionGroup
Case 1 ' opened selected
If IsNull(Me.DateClosed) Then
Me.DateOpened = VBA.date
Else
MsgBox "Issue has been closed.", vbExclamation, "Invalid Operation"
End If
Case 2 ' closed selected
If Not IsNull(Me.DateOpened) Then
Me.DateClosed = VBA.date
Else
MsgBox "Issue has not yet been opened.", vbExclamation, "Invalid
Operation"
End If
End Select
The number of days an issue has open at any particular date, or had been
open when closed, can computed with a function such as:
Function DaysOpen(varOpen, varClosed)
DaysOpen = DateDiff("d", Nz(varOpen, VBA.Date), Nz(varClosed, VBA.Date))
End Function
This can be called as the ControlSource property of an unbound control on
the form:
=DaysOpen([DateOpened],[DateClosed])
or as a computed column in the form's underlying query.
Ken Sheridan
Stafford, England been
:
Hi, I have set up a database to collect quality issues, I have my main table
and a query set up to show me information, i have created a form to allow for
data input based on the query. On the form I have an option group which is
basically got two options 'issue closed or issue open', is there anyway you
can link this option group to a counter so that when the user first ticks the
'issue open' box it starts a counter to count the number of days the issue
has been active?, and then stops counting days when the 'issue closed' tick
box is selected?. Hope you can help. Thanks Tony. (BruceM if your reading
this please see our last communication)
