The code below imports the file Modified Time and Date into a table. However the value in the table = '39491.4423611111' , it should be: 13-Feb-2008 10:37:00. I've tried changing the data types to "Date/Time" and "Text" and still get the same value. How can i change it so it show the correct value?

The code below show this:

** Please Note **
'coltext = Table name - this value was from the Open Dialog box
'sbuffer2 = the file name


 SQL = "Create TABLE " & coltext & "_FileDateAndTime (FileDate Text)"
 DoCmd.RunSQL SQL
 SQL17 = "ALTER TABLE " & coltext & "_FileDateAndTime ADD COLUMN FileTime Text"
 DoCmd.RunSQL SQL17


    Dim FName As String
    Dim Result As Boolean
    Dim TheNewTime As Double
    Dim WhatTime As FileDateToProcess

    FName = "E:\CSC\LDMS\LDMSDatabaseApp\MPI IFF Lorenzo\" & sBuffer2
    WhatTime = FileDateLastModified

    TheNewTime = GetFileDateTime(FileName:=FName, WhichDateToGet:=WhatTime)
    If TheNewTime < 0 Then
       Debug.Print "An error occurred in GetFileDateTime"
    Else

    sSQL = "INSERT INTO " & coltext & "_FileDateAndTime (FileDate) VALUES ('" & TheNewTime & "')"
    DoCmd.RunSQL sSQL

    End If

Try changing this: Dim TheNewTime As Double

To this: Dim TheNewTime As Date

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Yes it works :)

How could i get a time value from this aswell as the file also gets updated every 3 hours, thanks

Ooops, sorry, you need a double for some reason. Try this:

Dim TheNewTime As Double
Dim WhatTime As FileDateToProcess
Dim dtDateTime As Date

...

TheNewTime = GetFileDateTime(FileName:=FName, WhichDateToGet:=WhatTime)

dtDateTime = TheNewTime

The Double is needed to preserve the decimal places, which the previous change was eliminating.

Then post dtDateTime to here:

sSQL = "INSERT INTO " & coltext & "_FileDateAndTime (FileDate) VALUES (#" & dtDateTime & "#)"

if this field is a date/time field.

Did that work?




mmcdonal

Look it up at: http://wrox.books24x7.com