date formula

[dolphinkiki]

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

 

[Allen Browne]

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?

 

[dolphinkiki]

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.

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?

 

[Allen Browne]

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
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.

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?

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