timestamp

[Wijklodewijk]

access 2003 sp3 os xp
I want to query a table which as timestamp with 16 digit.
With serial I made a timestamp but it looks like 2,0080221194445E+15. I declared timestamp as double with long I get a overflow.
If I use this in my query in vba it doesn't except a comma.
If I use the same number when I use the design query it automaticly transform this number in 2008022119444500.
Any suggestions?

 

[gemma-the-husky]

declare timestamp as a date/time

thats what a time stamp is, isnt it?
it goes down to seconds only though, i think - not sure if it handles fractions of seconds

alternatively look at data type summary in help

double doesnt have enough precision for your requiremnts, hence the exponential format
decimal type has 28 digit precision, so you could use that for your purposes

 

[Moniker]

Why not just store the actual time instead of the serial number?

 

[Wijklodewijk]

Thanks for your reply. This timestamp is already created by a foxpro database. I only query the table with the timestamp. The timestamp is already there. I tried to create the same timestamp.
How do I change it into decimal. This is the code I created
dim varcuttime variant
dim cbltimestamp as double
dim strsqlcuttime as string
varcuttime =dateserial(Year(date), Month(date),day(date)) + Timeserial(hour(now()), Minute(now()),Second(now())
strsqlcuttime=cstr(format(varcuttime,"yyyymmddhhnnss")


cbltimestamp=cdbl(strsqlcuttime)*100

 

[Moniker]

Well, let's dissect that sample you gave:

2008022119444500

20080221 = 02/21/2008 (American mm/dd/yyyy format, stored as yyyymmdd)

19444500 = 7:44:45 pm

The last two digits are superfluous in Access (no milliseconds), so drop them. From there, you can use Format and string functions (left, mid, right) to pull the date and time out of that.

 

[Wijklodewijk]

I forgot the result
strsqlcuttime="20080226083030"
cbltimestamp=2,0080226083030E+15

And I want 2008022608303000

 

[Wijklodewijk]

Gemma your tip set me on the right track.
I found on the safari.oreilly.com that to use cdec the var has to be a variant instead of a double I used.

 

[Wijklodewijk]

Thanks you very much Gemma and Moniker. You both forced me to think and pushed me the right way.

With regards,

Lodewijk