date formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dolphinkiki - 28 Jan 2005 03:07 GMT

i am creating a database to produce help desk tickets. the problem i am
having is that i want to have the date field automatically put in the current
date only when the status of the ticket has been closed. I am at my witts
end!! Any help with this would be greatly appreciated!!! Thanks

Signature

Ready to pull my hair out!
dolphinkiki

Reply to this Message

Allen Browne - 28 Jan 2005 03:32 GMT

Presumably you have a yes/no field named Closed, and you want today's date
stored into a field named ClosedDate when this box is checked.

Use the AfterUpdate event procedure of the Closed check box on your form:

Private Sub Closed_AfterUpdate()
If Me.[Closed].Value Then
Me.[ClosedDate = Date
Else
Me.[ClosedDate] = Null
End If
End Sub

The question arises as to whether you really need the yes/no field.
Presumably if the ClosedDate field is null, that indicates that the job is
not yet closed?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>i am creating a database to produce help desk tickets. the problem i am
> having is that i want to have the date field automatically put in the
> current
> date only when the status of the ticket has been closed. I am at my witts
> end!! Any help with this would be greatly appreciated!!! Thanks

Reply to this Message

dolphinkiki - 28 Jan 2005 04:03 GMT

i am sorry for not wording it write. The field for the closed date has a look
up form. I can choose to have the ticket open working or closed resolved or
other options. What i am looking for is when I open this ticket the date and
time are automatically put in. (Took 2 days but i figured that one out! lol)
Now I want it to automatically put in the current date and time when I click
from open to close. I hope this relays what it is i am trying to do. I can
tell you that the field is not a yes/no field. thank you again.

> Presumably you have a yes/no field named Closed, and you want today's date
> stored into a field named ClosedDate when this box is checked.
[quoted text clipped - 17 lines]
> > date only when the status of the ticket has been closed. I am at my witts
> > end!! Any help with this would be greatly appreciated!!! Thanks

Reply to this Message

Allen Browne - 28 Jan 2005 04:50 GMT

So the field is a lookup into a table that has records like this:
1 Open
2 Working
3 Closed
4 Resolved

Use the AfterUpdate event of the combo where you select one of these values
to write the date and time to the field. The Event Procedure will look very
similar.

Private Sub Status_AfterUpdate()
If Me.Status = 3 Then
Me.ClosedDate = Now()
Else
Me.ClosedDate = Null
End If
End Sub

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>i am sorry for not wording it write. The field for the closed date has a
>look
[quoted text clipped - 34 lines]
>> > witts
>> > end!! Any help with this would be greatly appreciated!!! Thanks

Reply to this Message