Solved

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

Posted on 2006-06-24
7
456 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

707 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

9 Experts available now in Live!

Get 1:1 Help Now