Timestamping records

[Gary Schuldt]

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

 

[Bas Cost Budde]

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.

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.

 

[Jeff Boyce]

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

 

[Gary Schuldt]

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

 

[Gary Schuldt]

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

 

[Bas Cost Budde]

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.

 

[Bas Cost Budde]

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?

 

[Gary Schuldt]

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

 

[Guest]

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

 

[Gary Schuldt]

BCB,

of course it is cheating!! I wonder if there is a TimePart(datetime)
function?

Gary

 

[Tim Ferguson]

I wonder if there is a TimePart(datetime)
function?

yes...


Tim F

 

[Bas Cost Budde]

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)

 

[Gary Schuldt]

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

 

[Gary Schuldt]

Tim,

Thanks; see reply to Bas Cost Budde.

Gary

yes...


Tim F

 

[Bas Cost Budde]

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?

Sure. If you just need an equality test, Int() will do fine as wel.
Maybe DateValue is documenting better...

 

[Tim Ferguson]

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