Left$ query

[Dominic1980]

Left$([Entry timestamp],14)

I want to execute the above sql query but the result is not what i'm expecting.
the field entry that i wan to extract the leftmost 14 characters is
2.005113E+19
instead of just having plain numbers like 2005113009010532000000.....

I think it's becoz of the format that's why Left function is not working as expected. Can somebody help me out regarding this? Thanks very much

Regards
Dominic

 

[tkpstock]

Try

Left(CStr(Format([Entry timestamp],"0")),14)

HTH

 

[Dominic1980]

I tried that function. It does return me what i want but with decimals.
It returns me 20051130090132.00
But i need to convert this value into Date/Time format. If i change it to date/time with the decimal digits attached, the field will return null.

How do i remove the decimals behind the value?

Regards
Dom

 

[tkpstock]

What you are showing is seventeen characters - you are only grabbing the first 14, so where the data is ending up is probably your problem. Are you putting in a field formatted to two decimal places? I need more information...

 

[Dominic1980]

What you are showing is seventeen characters - you are only grabbing the first 14, so where the data is ending up is probably your problem. Are you putting in a field formatted to two decimal places? I need more information...

Hi Tom.

The situation is like this. I have a text file which has the value 20051130090132000000xxx which i grab and put it into a dbl field. Basically the text value 20051130090132000000xxx is date/time so i need to format it into date/time into my access database from text in a .txt file

I hope this is clear

 

[tkpstock]

Instead of a double field, try a long integer.

 

[Dominic1980]

I've put it into a text field and manipulate it into a date format so as to convert it into date/time. Thanks for all the help and advice

 

[Notsogood]

I have a similar problem, Dominic can you plz tell me how you converted text to Date/time? Thanks

 

[tkpstock]

I have a similar problem, Dominic can you plz tell me how you converted text to Date/time? Thanks

One way to do it is to grab parts of the string and force it to a date. Example:

st = "20060316"
yr = left(st,4)
mo = mid(st,5,2)
dy = right(st,2)
thedate = cdate(mo & "/" & dy & "/" & yr)

HTH