Solved

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

Posted on 2006-06-24
7
457 Views
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.

Thx!

Kitty

0
Comment
Question by:prettykittyq
7 Comments
 
LVL 26

Expert Comment

by:dannywareham
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.

Idea...?
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 250 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.

AW
0
 
LVL 1

Author Comment

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

Trying AW's recommendation.  Back soon.


Kitty
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 54

Expert Comment

by:nico5038
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 ?

Nic;o)
0
 
LVL 42

Expert Comment

by:dqmq
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?

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
 
LVL 44

Expert Comment

by:GRayL
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.
0
 
LVL 1

Author Comment

by:prettykittyq
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!

Kitty
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now