store time as a number

[Guest]

I have a general number field that I need to generate a unique number when
the record is created, but down the road, I have to be able to change it and
even duplicate a previously used number.

Therefore, autonumber doesn't work. I have tried to establish the current
date and time down to the second in the format of a number, but I cannot find
how to write that expression. Please help (I am in access 2003)

 

[Ken Snell [MVP]]

Add a field to your table - set its Data Type to Date/Time. Then store the
time into that field using a function (Time()). Or store date and time there
using Now() function.

 

[Guest]

Thanks, but I need to store it as a number and if I do time, tomorrow, if I
create a record at the same time, I will have duplicate entry - which won't
work. I have to always have a unique number (when it is created for the
first time).

I want to store (10/10/2005 14:05:26 (for example - knowing that changes
every minute) as a unique, never again to be duplicated number. Is that
possible?

 

[Douglas J. Steele]

The Now function returns both date and time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Ken Snell [MVP]]

As I said, use a Date/Time field in your table. Use the Now() function to
get the exact time and date, and store it in that field. ACCESS stores dates
and times as a floating point number. For example,
10/8/2005 4:29:47 PM

is stored as this number:
38633.687349537

38633 is the number of days since December 30, 1899.

..687349537 is the fraction of a 24-hour day represented by 4:29:47 PM.


--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

But that only returns the minutes - I cannot get access to show me the
seconds in a number format!

 

[Guest]

Ken - thanks for your help... and patience!

As I said, use a Date/Time field in your table. Use the Now() function to
get the exact time and date, and store it in that field. ACCESS stores dates
and times as a floating point number. For example,
10/8/2005 4:29:47 PM

is stored as this number:
38633.687349537

38633 is the number of days since December 30, 1899.

..687349537 is the fraction of a 24-hour day represented by 4:29:47 PM.

 

[Guest]

Try:

Me!txtUniqueNum.Value = CDbl(Now)

.. . . where txtUniqueNum is a text box bound to a Double data type field.
However, be aware that this is not guaranteed to be unique, since different
computers have different system times. For example, if you created a new
record -- which generates a certain number using this scheme -- and my system
clock is exactly 37 seconds slower than yours and I create a new record 37
seconds after you created yours, these two records will have the same number.
This is an unlikely scenario, but certainly possible. And as Doug Steele
stated in another thread of yours, you can't guarantee that the system clock
won't be reset, either.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Douglas J. Steele]

Time is accurate to the second.