Where do I get that Access "funky date/time"?

Posted on 2006-06-24
Medium Priority
Last Modified: 2008-01-09
I'm creating a database for a surgeon to catalog the films he has made of cataract operations he has performed over the past 7 years.

For legal reasons, I need to define a primary key for each record that can't be recognized as part of the medical record (else some snotty malpractice lawyer could figure out which operation referred to his patient and use it against us).

I don't want to use AutoNumber.  So, I've been thinking that I could generate a record number based on the exact time (to the hundredths of a second) when the record was entered into the database.

I seem to remember Access internally views date and time in some funky format that results from treating the specified date and time as fractions of 1, but I haven't been able to find a description of that phenomenon and I don't know how to get Access to cough up its funky time in its funky format.  It's coughed it up by accident for me in the past but, naturally, I can't get it to do it on demand.

Any suggestions would be most appreciated.



Question by:prettykittyq
LVL 26

Expert Comment

ID: 16974965
Hi Kitty

You're right that Access stores dates/times as a long number.
It represents the number of days that have elapsed since 30th Dec 1899.
The decimal of the number represents the portion of the day.

Unfortunately, although Access stores this data, it's not really available to users directly.

If you want a random number, that doesn't really mean anything, why not change the autonumber to replication ID (in the bottom window in table design view). Then you can use the 2nd to 7th characters (for exampel) as an ID

Anotehr alternative is to create a random number based on the exact date time:

MyField = RND(Now()) * 100

This will return a random number for you.

LVL 44

Accepted Solution

Arthur_Wood earned 1000 total points
ID: 16975070
actually, <dannywarren> is not quite correct.  If you'convert' the Date/Time value to a DOUBLE, then you can use the Numeric form of the 'date', and if you MULTPLY the double value by 10000, you can store the value as a LONG INTEGER, which should be unique (I doubt that you are adding records to the database faster the 1000 per second)

So, if you have the Date/time when the record was created, by setting the variable to Now, convert that to a double, and multiply by 10000, toget a LONG integer, like this:

Dim MYPrimaryKey value as Long
Dim MyConvertedDate as Double
Dim MyDate as Date

MyDate = Now()   ' gets the system clock time, with Time included

MyConvertedDate = cDbl(MyDate)
MYPrimaryKey = cLng(MyConvertedDate * 10000)

and use MYPrimaryKey  as the Primary Key of the record when you add it to your table (with the Primary Key field defined as Number/Long Integer), then you should be fine.


Author Comment

ID: 16975103
Thanks for the quick responses.  Gotta love EE.

Trying AW's recommendation.  Back soon.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 54

Expert Comment

ID: 16975224
When you change the Autonumber's Fieldsize from "Long Integer" into "Replication ID", you'll get what we call a GUID (Short for: Globally Unique Identifier) like: {7B0A3137-A57B-49DC-B632-5F0953785CE0}

Access uses the same "trick" for replicated databases to be sure nu duplicates are created.

Idea ?

LVL 42

Expert Comment

ID: 16975754
AW, please correct me, but wouldn't multiplying the timestamp by 3,600, give an integer precision of 1 minute? Therefore, multiplying by 10,000 would be roughly 1/3 that or 20 seconds.  What am I missing?

I not too fond of autonumber's either.  But almost any solution with random numbers or timestamps has the potential for collisions.  For example, maybe hundredth's of a second is good enough for one user, but what about 2?

The KISS principal says just use the next sequential number, ie: DMAX("OperationID","Operation")+1.
LVL 44

Expert Comment

ID: 16976518
It depends how you build the ID.  If you create records manually and use datetime as the source for the Primary Key, and you do not create records at a rate greater than 1 per second, you can use the datetime as a primary key.  When you look at the number created to represent 1 second  to 59 seconds, you will find that the difference between seconds varies between 0.0000115 and 0.0000116.  Thus you can take the decimal value for the datetime created by Access, and plus or minus 0.0000115/2 and Access will still give you the same datetime.  Times 10,000 is far too coarse to use with any accuracy.

If you create the database, and then generate the PK using VB where you are adding PK's at the rate of hundreds or thousands per second, then dqmq's approach is the only safe one.

Author Comment

ID: 16979838
AW, it worked like a charm once today's Formula 1 race was over and I got a chance to focus on your answer to my question.  Thanks!

dqmq and GRayL, thanks very much for the input.  I'll keep it in mind for future projects.  But, as it happens, no user of this database is going to be adding records at anything like hundreds or thousands per second, so AW's code worked just fine for me.

Thanks, all!


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question