Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2006-06-24
7
459 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

860 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