A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

date formula



 
 
Thread Tools Display Modes
  #1  
Old January 28th, 2005, 04:07 AM
dolphinkiki
external usenet poster
 
Posts: n/a
Default date formula

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
--
Ready to pull my hair out!
dolphinkiki
  #2  
Old January 28th, 2005, 04:32 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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 - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dolphinkiki" wrote in message
...
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
--
Ready to pull my hair out!
dolphinkiki



  #3  
Old January 28th, 2005, 05:03 AM
dolphinkiki
external usenet poster
 
Posts: n/a
Default

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.

"Allen Browne" wrote:

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 - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dolphinkiki" wrote in message
...
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
--
Ready to pull my hair out!
dolphinkiki




  #4  
Old January 28th, 2005, 05:50 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dolphinkiki" wrote in message
...
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.

"Allen Browne" wrote:

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" wrote in message
...
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
--
Ready to pull my hair out!
dolphinkiki



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting up a date formula in a spreadsheet Glenstolp Worksheet Functions 1 October 5th, 2004 11:49 PM
Help with date formula [email protected] Worksheet Functions 2 May 22nd, 2004 05:25 PM
Date being converted to formula Jason Morin Worksheet Functions 1 May 20th, 2004 07:31 PM
Date controlled formula Silver Worksheet Functions 4 February 6th, 2004 05:10 PM
formula needed to calculate a date Louis Worksheet Functions 2 December 2nd, 2003 07:07 AM


All times are GMT +1. The time now is 02:51 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.