Counting days using option group

[blake7]

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 you have IssueOpenDate and IssueClosedDate fields there is no need I can
see for the option group. Just have the user enter the OpenDate and the
CloseDate, or use a command button or something to insert today's date into
the fields.
To answer your specific question, you could use the option group's After
Update event to set the value of the date fields:

If Me.grpIssue = 1 Then
Me.IssueOpenDate = Date
Else
Me.IssueCloseDate = Date
End If

but again there is no reason to store redundant data.

 

[blake7]

Thanks for your help and comments bruce, I can see your point but at a later
date i would like to run a query to show which issues are still open so i can
create a report to send to my team members and was going to use the 'Issue
open' as a criteria, what do you think? .Thanks Tony

 

[BruceM]

You already have an "issue open" condition, which is the absence of a value
in IssueCloseDate. Your query's criteria can include a test for a value in
IssueCloseDate. In query design view the criteria row for IssueOpenDate
could be:
Is Not Null
and for IssueCloseDate
Is Null
You may not need to test for Is Not Null in IssueOpenDate if every issue is
given a date as soon as it is started, perhaps today's date as the default,
when the issue is opened.
A report based on the query will show only the open issues.

 

[blake7]

Ah yes I see your train of thought Bruce, that makes sense. Thank you very
much.
Tony

 

[BruceM]

Glad to help. I learned about it here. Once you assimilate such concepts
you are well on your way to more efficient and coherent designs.

 

[blake7]

Hi Again Bruce, I ran your idea past my manager which he agreed was great for
prompting team members to close off issue etc, but he really wanted the
visual count on the screen as he said it has more impact, so my question is
can you actually make a text box show the days count when the option group is
set to open and stop it when it has been closed ? Thanks Again Bruce.
Regards Tony

 

[BruceM]

I'm not sure I follow the question. You can add a text box to the form or
report footer with the following as its record source:
=Count(*)
or you could expand that to something like:
="There are " & Count(*) & " open issues"
Count(*) will show you the same number as you see in the built-in record
navigation box, but you can format it and locate it as you choose.

The thing I don't understand in the question is "the days count when the
option group is set to open and stop it when it has been closed".
What is this option group, and what exactly do you mean by "open" and
"closed"?

 

[blake7]

Sorry Bruce, i must be confusing the hell out of you, the form when run via
the query is showing a selection from the 100 or so entries assigned to a
team member, each individual quality issue has a unique reference number, the
form has various details relating to the issue, one of these we want to show
is how long the issue has been open for in days (open means issue not yet
been resolved) this is a visual reminder to the team member that the issue is
still 'open', the problem is freezing the open date, because using =date()
function in any check boxes always returns the value of the current day etc.
I just want the current date to enter a field on the form when the user
clicks 'date opened' which starts a visual counter on screen to count the
days elapsed and the other date (date closed) to enter another field on the
form, this then stops the counter. I promise I will give up if this cannot be
done. Regards Tony

 

[BruceM]

It can be done. The problem is that you are looking at your project and I
am not. A text box (along with just about anything you can put on a form or
report) is a control. Some controls such as lines and labels are there to
help the user, but cannot be tied directly to a field in the form's or
report's Record Source. Other controls such as text boxes may be bound to a
field (the field is the Control Source for that control) or unbound. An
unbound text box with the Control Source =Date() will always show today's
date when you navigate to any record. To have the text box show a stored
date you need to set the text box Control Source to a date field in the
form's or report's Record Source, and you need to store the date in that
field. If you have a command button to start a new Issue, it's Click Event
could be:

Me.[IssueOpenDate] = Date

You may want to lock things down so that it is only possible to enter the
date just once. One possibility is something like this in the command
button Click event:

If Me.NewRecord Then
Me.[IssueOpenDate] = Date
End If

Or maybe be in the form's Current event:

If Not IsNull(Me.[IssueOpenDate]) Then
Me.txtIssueOpenDate.Locked = True
End If

There are other options, depending on your specific needs. IssueOpenDate is
the name of the table field, and txtIssueOpenDate is the name of the text
box bound to the field. It is best if they have different names, although
Access gives them the same name by default. Use names of your own choosing,
but it is best if they contain only numbers, letters, and underscores. If
they contain anything else, including spaces, the name must be enclosed in
square brackets; otherwise the square brackets are not necessary.

For the counter, an unbound text box could have the Control Source:
=DateDiff("d",Date,[IssueOpenDate])

 

[blake7]

Thanks Bruce, did what you suggested all is working fine, sorry for
confusion. Regards Tony

It can be done. The problem is that you are looking at your project and I
am not. A text box (along with just about anything you can put on a form or
report) is a control. Some controls such as lines and labels are there to
help the user, but cannot be tied directly to a field in the form's or
report's Record Source. Other controls such as text boxes may be bound to a
field (the field is the Control Source for that control) or unbound. An
unbound text box with the Control Source =Date() will always show today's
date when you navigate to any record. To have the text box show a stored
date you need to set the text box Control Source to a date field in the
form's or report's Record Source, and you need to store the date in that
field. If you have a command button to start a new Issue, it's Click Event
could be:

Me.[IssueOpenDate] = Date

You may want to lock things down so that it is only possible to enter the
date just once. One possibility is something like this in the command
button Click event:

If Me.NewRecord Then
Me.[IssueOpenDate] = Date
End If

Or maybe be in the form's Current event:

If Not IsNull(Me.[IssueOpenDate]) Then
Me.txtIssueOpenDate.Locked = True
End If

There are other options, depending on your specific needs. IssueOpenDate is
the name of the table field, and txtIssueOpenDate is the name of the text
box bound to the field. It is best if they have different names, although
Access gives them the same name by default. Use names of your own choosing,
but it is best if they contain only numbers, letters, and underscores. If
they contain anything else, including spaces, the name must be enclosed in
square brackets; otherwise the square brackets are not necessary.

For the counter, an unbound text box could have the Control Source:
=DateDiff("d",Date,[IssueOpenDate])

Sorry Bruce, i must be confusing the hell out of you, the form when run
via
the query is showing a selection from the 100 or so entries assigned to a
team member, each individual quality issue has a unique reference number,
the
form has various details relating to the issue, one of these we want to
show
is how long the issue has been open for in days (open means issue not yet
been resolved) this is a visual reminder to the team member that the issue
is
still 'open', the problem is freezing the open date, because using =date()
function in any check boxes always returns the value of the current day
etc.
I just want the current date to enter a field on the form when the user
clicks 'date opened' which starts a visual counter on screen to count the
days elapsed and the other date (date closed) to enter another field on
the
form, this then stops the counter. I promise I will give up if this cannot
be
done. Regards Tony