How do I save the date a record was last modified in Access 2003
in [Tables]
|
Prev: auto number
Next: Blank Field in Form
From: Al Campagna on 29 Dec 2009 19:23 Did you read Clifford Bass's response. He makes additional good points to consider... -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9CF078F60B266f99a49ed1d0c49c5bbb2(a)74.209.136.90... > "Clifford Bass via AccessMonster.com" <u48370(a)uwe> wrote in > news:a148c2c00e97c(a)uwe: > >> I find setting the default values for date/time fields in Access >> to Date (), Now() or Time() to be highly inaccurate because the >> value is set when the new row is displayed, not when data is >> initially being entered and not when it is actually saved. So if >> I go to a new record on 12/28/2009 at 11:50 pm, but do not >> actually start entering anything until 12/29/2009 at 12:15 am and >> do not actually save it until 12/29/2009 at 12:45 am, it will save >> with a creation date of 12/28/2009 and a creation time of 11:50 >> pm. Which is just plain wrong. > > That's something I never considered. > > But I still prefer the default values in the table, nonetheless. > > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Armen Stein on 31 Dec 2009 10:28 On 29 Dec 2009 16:52:00 GMT, "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote: >It is perhaps preferable to *you* -- for me, I vastly prefer a >date-only field. Where I need the time value, I use a separate >column for the time part. >But it's much harder to query date fields with time parts, >particularly if you are only ever displaying the date part. Hi David, I understand your preference. However, once one gets the hang of querying dates with time components, it's easy. The time and data components can be extracted from one field when necessary. Date/Time math is easier when they're in one field. And having them in one field takes half the space. Armen Stein Microsoft Access MVP www.JStreetTech.com
From: Clifford Bass via AccessMonster.com on 31 Dec 2009 11:13 Hi Al, Thanks for your comments (about my comments)! Clifford Bass Al Campagna wrote: >Did you read Clifford Bass's response. He makes additional good >points to consider... -- Message posted via http://www.accessmonster.com
From: David W. Fenton on 31 Dec 2009 16:48 Armen Stein <ArmenStein(a)removethisgmail.com> wrote in news:fggpj5p0lkpph5m72e5rsg0rl80pm4hd3c(a)4ax.com: > On 29 Dec 2009 16:52:00 GMT, "David W. Fenton" ><XXXusenet(a)dfenton.com.invalid> wrote: > >>It is perhaps preferable to *you* -- for me, I vastly prefer a >>date-only field. Where I need the time value, I use a separate >>column for the time part. > >>But it's much harder to query date fields with time parts, >>particularly if you are only ever displaying the date part. > > I understand your preference. However, once one gets the hang of > querying dates with time components, it's easy. The time and data > components can be extracted from one field when necessary. > Date/Time math is easier when they're in one field. 99% of my querying against these fields is by date, and while it's not that hard for *me* to query with: WHERE DateField >= #12/18/2009# AND < #12/19/2009# ....but if you have user populations who write their own queries, this is more than I want to ask them. On the other hand, these are not fields that they are most likely to be querying on. Aggregation on these date fields is also more complicated, as you have to do it on an expression (DateValue(Created)) instead of on the base field. Again, likely not a big deal, but it could be a performance drain on a large table. It could also lead to issues if you're using a server back end instead of Jet/ACE -- it could force the aggregation client-side instead of server-side unless you move the logic for doing the aggregation to a server-side component. In general, my apps don't need the time component at all for the created/updated fields. About the only ones that do are logging tables, where time really is crucial. But even in that case, I'm mostly searching on date ranges and ordering by time for each date. That is, the time component is used only for ordering and not for querying. > And having them in one > field takes half the space. This is a difference that is not in any way relevant in our modern operating environment. The only criticism in this direction I can see that would be significant is if you index both fields, as maintaining two indexes could add to overhead enough to be noticeable, or you could bump up against the 32 indexes limit. I was just away from the computer for a moment and realized that I have an antipathy towards Now() that comes from its frequent mis-use in a lot of the Access apps that I've encountered and been required to update. A field populated with Now() and with a format of Short Date is a red flag to me -- it's not that you won't sometimes want to omit the time component for display, it's just that if that's the only way it's displayed, then it's being populated with the wrong function. Another example is Invoice Dates populated with Now() -- that's quite clearly an error, in my opinion, but I encounter that kind of thing frequently (especially in apps created a long time ago, in particular by an odd fellow named David Fenton who back in 1997 or so wasn't all that experienced with Access and created some really bad messes). I think a lot of the Access sample apps and the report wizards use Now() and then format it for display. I can sort of see this, as it makes them more forgiving for novice users. But it also trains them to not choose the right function for the job. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Armen Stein on 31 Dec 2009 18:46
On 31 Dec 2009 21:48:13 GMT, "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote: Hi David, >...but if you have user populations who write their own queries, >this is more than I want to ask them. On the other hand, these are >not fields that they are most likely to be querying on. Well, we write applications where few (if any) user write their own queries. Instead, we build forms where they can specify their own criteria, and we handle the SQL. But I see your point if users are rolling their own queries. >> And having them in one >> field takes half the space. > >This is a difference that is not in any way relevant in our modern >operating environment. Obviously disk drives are vast and getting more so. I often advocate for less efficient storage if there's a good reason. But if the same information can literally be put into a smaller space, then I would lean toward doing so. Of course, I also don't think that handling the time component is a big deal. >Another example is Invoice Dates populated with Now() -- >that's quite clearly an error, in my opinion, but I encounter that >kind of thing frequently I agree. It's an error to store the time component when it isn't relevant. Almost all of our date fields have just the Date in them. But timestamping when a record is created or changed can actually be useful. We have a client where the boss works late. Whenever we see activity in the evening, we know it's his fault. <s> Armen Stein Microsoft Access MVP www.JStreetTech.com |