Querying on date fields originally populated with value from FileDateTime

[PatThePostie]

I have a table in Access that contains a list of file names and their modified dates, which is used to check if a certain file in a filing system has changed. The file modified date field is populated using the output of FileDateTime. However, whenever I query this table using the file name and the file modification time, where the file modification time is determined using the FileDateTime function again, it always fails. I've had to resort to do a look-up on the file name only, then using VBA code to check if the returned file modification date field is the same as the value returned from the file system for the same file using the FileDateTime function and this works no problem.


In writing the data to the table using VBA code, specifically an ADODB recordset. When creating the SQL query, I'm parsing the output of the FileDateTime function, that was performed on the file, through code to convert it to an SQL date, thus:

Function SQLDateTime(DateTimeToConvert As Variant) As String
      SQLDateTime = "#" & Format(CDate(DateTimeToConvert), "mm/dd/yyyy hh:nn:ss") & "#"
  End Function

Any idea where I'm going wrong? :banghead:

 

[theDBguy]

What does "always fails" mean? Would it have anything to do with files modified in the afternoons? If so, maybe your format should be more something like:

"mm/dd/yyyy hh:nn:ss ampm"

Just curious...