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

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.

Thx!

Kitty

LVL 1
prettykittyqAsked:
Who is Participating?
 
Arthur_WoodConnect With a Mentor Commented:
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.

AW
0
 
dannywarehamCommented:
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.

Idea...?
0
 
prettykittyqAuthor Commented:
Thanks for the quick responses.  Gotta love EE.

Trying AW's recommendation.  Back soon.


Kitty
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nico5038Commented:
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 ?

Nic;o)
0
 
dqmqCommented:
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?

kitty,
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.
0
 
GRayLCommented:
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.
0
 
prettykittyqAuthor Commented:
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!

Kitty
0
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.

All Courses

From novice to tech pro — start learning today.