 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / February 2004Timestamping records
I want to have two fields for each record in tTable: DateCreated DateLastUpdated I use a form to update the table. Can someone please point me to the design set up for this functionality? I would want to do some simple queries, such as select all records where either one of these values = current date. Thanks. Gary > I want to have two fields for each record in tTable: > [quoted text clipped - 6 lines] > I would want to do some simple queries, such as select all records where > either one of these values = current date. In the BeforeUpdate event of the form, put me!dateLastUpdated = date() In the BeforeInsert event (or maybe AfterInsert, play/test that), have me!dateCreated = date() And, no, in the Delete event you cannot have me!dateDeleted = Date() ;-) although sometimes I write the record to be deleted into some backup table. A lot of work.  SignatureBas Cost Budde http://www.heuveltop.org/BasCB but the domain is nl Yeah, I wish there was better auto-logging support in Access! Just curious why you would think that it might be the AfterInsert event? BeforeInsert seems to make more sense, but what does a newbie know?? And thanks! I wasn't even conscious about these events. But, then, I don't have a PhD in Access, either. Gary > > I want to have two fields for each record in tTable: > > [quoted text clipped - 26 lines] > http://www.heuveltop.org/BasCB > but the domain is nl > Yeah, I wish there was better auto-logging support in Access! > > Just curious why you would think that it might be the AfterInsert event? > BeforeInsert seems to make more sense, but what does a newbie know?? I trust newbies to know how to put a fresh view before oldtimers :-) I couldn't find an eample of my own usage of timestamping so I included this uncertainty. > And thanks! I wasn't even conscious about these events. But, then, I don't > have a PhD in Access, either. I think MVP is the "highest recognized grade" around :-) Be curious and read through property sheets, it helps. SignatureBas Cost Budde http://www.heuveltop.org/BasCB but the domain is nl Yes, sometimes fresh (i.e., "naive") views do appear! re: "Be curious . . . " Well, I guess I'm pursuing more a Voc Tech degree in Access. If I were after a PhD, the curiosity would be essential, I agree. Gary > > Yeah, I wish there was better auto-logging support in Access! > > [quoted text clipped - 15 lines] > http://www.heuveltop.org/BasCB > but the domain is nl Gary If you can safely assume that when a new record is created via your form, you can use the current date, use the Date() function as a default value. NOTE: using the Now() function stores date AND time info, and makes querying difficult, as you'd need to include both date AND time elements as criteria ... or use the DatePart() function to strip out only the date portion. For the LastUpdate field, in your form's BeforeUpdate event, run through any/all validations you need to do, then set the value of the LastUpdate field to Date() (same caution as above). SignatureGood luck Jeff Boyce <Access MVP Thanks, Jeff; it makes sense. I did notice that Now() contains both date and time info, but didn't know about the DatePart() function. I'll give it a go. Gary > Gary > [quoted text clipped - 15 lines] > Jeff Boyce > <Access MVP > Thanks, Jeff; it makes sense. I did notice that Now() contains both date > and time info, but didn't know about the DatePart() function. Dates/times are stored as integer/fraction. So, if you want to compare two timestamp fields on their date value, you could also use their int(field) value. Or is that considered cheating? SignatureBas Cost Budde http://www.heuveltop.org/BasCB but the domain is nl BCB, of course it is cheating!! I wonder if there is a TimePart(datetime) function? Gary > > Thanks, Jeff; it makes sense. I did notice that Now() contains both date > > and time info, but didn't know about the DatePart() function. [quoted text clipped - 7 lines] > http://www.heuveltop.org/BasCB > but the domain is nl > I wonder if there is a TimePart(datetime) > function? yes... Tim F Tim, Thanks; see reply to Bas Cost Budde. Gary > > I wonder if there is a TimePart(datetime) > > function? > > yes... > > Tim F > BCB, > > of course it is cheating!! I wonder if there is a TimePart(datetime) > function? No need to wonder. Ctrl-G, type the word, press F1 (or a space for the parameter lister) SignatureBas Cost Budde http://www.heuveltop.org/BasCB but the domain is nl OK, Tim and BCB, I do what you recommend and I don't get a hit on TimePart :-( But I do see the DateValue and TimeValue functions. If I store Now() in DateLastUpdated in the Form's BeforeUpdate event, it would seem that I could select records where DateValue(DateLastUpdated) = DateValue(Now()) to see all the records updated today, no? Gary > > BCB, > > [quoted text clipped - 8 lines] > http://www.heuveltop.org/BasCB > but the domain is nl > OK, Tim and BCB, > [quoted text clipped - 5 lines] > would seem that I could select records where DateValue(DateLastUpdated) = > DateValue(Now()) to see all the records updated today, no? Sure. If you just need an equality test, Int() will do fine as wel. Maybe DateValue is documenting better... SignatureBas Cost Budde http://www.heuveltop.org/BasCB but the domain is nl > But I do see the DateValue and TimeValue functions. Oops: quite right! I was thinking of TimeValue... The DatePart() function is of course completely different: it is used to shell out any individual part of a DateTime variable, so you can use wThisYear = DatePart("yyyy", Now()) ' year wThisMinute = DatePart("nn", Now()) ' minutes The parameters are defined in Help: if F1 doesn't work (and if often doesn't) just type DatePart into the Index. HTH Tim F For the DateCreated, set the default value in the table design to Now(), and it will automatically record the time the record was created. For the DateLastUpdated, put the field on the form with Visibility=False. Have a BeforeUpdate event for the form. In the event set DateLastUpdated=Now(). >-----Original Message----- >I want to have two fields for each record in tTable: [quoted text clipped - 13 lines] > >. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.