Convert Date and Time to a Whole Number (1 Viewer)

DavidWE · Sep 26, 2011

Is there a way to convert the date and time value returned by the Now() function into a whole number. I am attempting to group scans by a unique number. When the users clicks a "Save" button, I would like to save that unique number to a table. Each scan will be one record stored in a recordset until the user clicks Save. All of the records created in a recordset would have the unique number as a field. I want to include time to make sure the number is unique.

Another alternative would be to save a unique number to a table and read it each time the form is loaded. It would then be incremented each time the scans are saved.

I would like to see how the first option works before I decide. Does anyone have any suggestions?

Thank you.

bwellbor · Sep 26, 2011

You could try using the Format function. One of these could work:

Format(Now(),"mmddyyyyhhmm")
Format(Now(),"00000")
Format(Now(),"00000.0000")

You can play with the formatting until you get a number you like.

Guus2005 · Sep 27, 2011

A date is a double.

Clng(Date()) results in a number of days since December 30, 1899
Time is stored behind the decimal point. It depends on how many digits you want.

HTH

CBrighton · Sep 27, 2011

CInt() works the same as above and integers are whole numbers only.

However, you may want to test that it rounds decimals as you would expect.

vbaInet · Sep 27, 2011

A date is a Double like Guus mentioned and you use a Long (not an Integer) to cast it to type Number. You will potentially get an overflow if you cast to Integer.

gemma-the-husky · Sep 27, 2011

the integer part of the date represents the number of days since a date in 1899, and therefore this year is about day 40000 (110 x 365).

the decimal part of the date represents the fractional day - so 12 hours is 0.5 of a day.

a longint (4 digit int, goes up to about 2billion ie 2+9 zeroes) , so I think you could safely multiply a date by 10000, and store as an integer, as this will not overflow.

you could then divide by 10000 to get the original date back. You MIGHT lose a tiny bit of precision in the time by doing this.

CBrighton · Sep 27, 2011

What if you do CInt(Round()) so it's converting a double which holds a whole number??

I only mentioned int because he specifically asked for a way to convert it into a whole number rather than something with decimals.

DavidWE · Sep 27, 2011

Thanks for all of the replies. I've tried several of the recommendations above and can use one of them or a variation. And yes, I do get an overflow if I'm not careful to round first and cast to an integer.

vbaInet · Sep 27, 2011

Good to hear. It looks like you used the CInt(Round()) option? I would still vote for Guus' CLng() because you are only performing one function as opposed to two and CLng doesn't return a Double, it returns a signed Int.

DavidWE · Sep 28, 2011

I did try Clng(Date()) but the number is not always unique since the user might do several saves on one date. I also tried Clng(Now()) and the number still was not always unique. I had to add seconds to make the number unique - Format(Now(),"mmddyyyyhhmmss").

vbaInet · Sep 28, 2011

Alright, I see. You wanted a unique number.

Does CLng(Format()) work?

DavidWE · Sep 28, 2011

vbaInet said:
Does CLng(Format()) work?

I got some overflows with the above even though I used a double. It is possible I made a mistake somewhere.

vbaInet · Sep 28, 2011

Are performing any multiplication or addition somewhere?

nschroeder · Sep 28, 2011

Why don't you just make your tables' primary key an autonumber?

gemma-the-husky · Sep 28, 2011

not tested, but as i said, to include the time element you need something like this

longdate = clng(somedate * 10000)

you can then get it back by saying

somedate = cdate(longdate/10000)

I am sure you would not get an overflow with this

============
why don't you just try dome different versions of these examples - see which one you like best.

it isn't actually writing the queries etc that takes the time. as always, you will find that you spend most time managing/confirming the integrity of the data

ie non-overlapping times , both clock in and clock out for each event. etc

DavidWE · Oct 3, 2011

vbaInet said:
Are performing any multiplication or addition somewhere?

I'm late responding. No, I don't think I was performing multiplication or addition. I haven't had a chance to look at it for about a week. I'll check again and see about the overflow.

vbaInet · Oct 3, 2011

If you attach a stripped down version of your db (that hasn't been tested as not working), we could have a quick look.

DavidWE · Oct 3, 2011

nschroeder said:
Why don't you just make your tables' primary key an autonumber?

I already have the primary key set as an autonumber. I need to generate another number to link records together. Each of those records will have its own primary key in addition to the number the code generates. I was attempting to make the number meaningful with the date and time.

DavidWE · Oct 3, 2011

gemma-the-husky said:
not tested, but as i said, to include the time element you need something like this

longdate = clng(somedate * 10000)
you can then get it back by saying

somedate = cdate(longdate/10000)
I am sure you would not get an overflow with this

Thanks. I will try it again. It might be a few days.

DavidWE · Oct 3, 2011

vbaInet said:
If you attach a stripped down version of your db (that hasn't been tested as not working), we could have a quick look.

I'll try a few things first and then post my final solution in case anyone else needs it. I'll attach the db if I don't get it working without errors.

Thanks

Convert Date and Time to a Whole Number (1 Viewer)

Registered User.

Registered User.

AWF VIP

Surfing while working...

AWF VIP

Super Moderator

Surfing while working...

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

nschroeder

Super Moderator

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread