FileDateTime function in a query

[Jeff]

Short question: Will the FileDateTime function work in an Access
query? Or is there another way to read the Date Modified attribute of
a file from an Access query?

Long story: (descriptions and code are simplified with generic names
for clarity)
I'm trying to create a query to check whether all the documents in a
folder are up-to-date.

I have a database with a table of a large number of documents. The
table has a field "RevisionDate". The actual documents (in pdf
format) are in a folder that may be located anywhere (on the local
computer or on a server in another location). I created a custom
database property to hold the location of the Documents folder. To
open a document, I select the desired document from a listbox on a
form and concatenate the DocumentLocation and the DocumentName, e.g.:
----
Dim pdffile as string.

pdffile = [DocumentLocation] & "\" & [DocumentName]
Application.FollowHyperlink pdffile, , True
----
On the form, I can check the date of individual documents by reading
the Date Modified attribute of the file and comparing it to the
[RevisionDate] field.
----
pdfdate = Format(FileDateTime(pdffile), "Short Date")
If CDate(RevisionDate) > CDate(pdfdate) Then
MsgBox "Error - The selected document is outdated!"
End If
----
But I have to scroll through a long list to check each one, so I would
like to create a query to find all outdated documents. When I try to
use FileDateTime in the query, I get an error:
Undefined function "FileDateTime" in expression.
----
SELECT [Documents].[Record_ID], [Documents].[DocumentName],
[Documents].[RevisionDate], [DocumentLocation] & "\" & [DocumentName]
& ".pdf" AS PDFfile, CDate(Format(FileDateTime([pdffile]),"Short
Date")) AS pdfdate FROM [Documents] WHERE
(((CDate(Format(FileDateTime([pdffile]),"Short Date"))) <
CDate([RevisionDate])));
----

However it says on this page that you can use the FileDateTime
function in a query:
http://www.techonthenet.com/access/functions/file/filedatetime.php

I thought I might have something formatted incorrectly, so I have
tried simplifying the expression to just list the DateTime of the file
(as opposed to converting it to a Short Date format and comparing it
to the RevisionDate); but it still tells me that FileDateTime is an
undefined function.

So, can someone tell me if FileDateTime is a valid query function or
if it works only in VBA? And if not, is there another way to
accomplish what I'm trying to do? Thanks much.

 

[Phil Hunt]

You need to define the function FileDateTime
in a module, any module as long as it is public.

 

[Jeff]

Okay, thanks! I didn't know you could define a function for a query. I
had thought it was an Access built-in function since it worked in VBA
on the form. Now I'll have to try to figure out how to do that.

You need to define the function FileDateTime
in a module, any module as long as it is public.

Short question: Will the FileDateTime function work in an Access
query? Or is there another way to read the Date Modified attribute of
a file from an Access query?

Long story: (descriptions and code are simplified with generic names
for clarity)
I'm trying to create a query to check whether all the documents in a
folder are up-to-date.

I have a database with a table of a large number of documents. The
table has a field "RevisionDate". The actual documents (in pdf
format) are in a folder that may be located anywhere (on the local
computer or on a server in another location). I created a custom
database property to hold the location of the Documents folder. To
open a document, I select the desired document from a listbox on a
form and concatenate the DocumentLocation and the DocumentName, e.g.:
----
Dim pdffile as string.

pdffile = [DocumentLocation] & "\" & [DocumentName]
Application.FollowHyperlink pdffile, , True
----
On the form, I can check the date of individual documents by reading
the Date Modified attribute of the file and comparing it to the
[RevisionDate] field.
----
pdfdate = Format(FileDateTime(pdffile), "Short Date")
If CDate(RevisionDate) > CDate(pdfdate) Then
MsgBox "Error - The selected document is outdated!"
End If
----
But I have to scroll through a long list to check each one, so I would
like to create a query to find all outdated documents. When I try to
use FileDateTime in the query, I get an error:
Undefined function "FileDateTime" in expression.
----
SELECT [Documents].[Record_ID], [Documents].[DocumentName],
[Documents].[RevisionDate], [DocumentLocation] & "\" & [DocumentName]
& ".pdf" AS PDFfile, CDate(Format(FileDateTime([pdffile]),"Short
Date")) AS pdfdate FROM [Documents] WHERE
(((CDate(Format(FileDateTime([pdffile]),"Short Date"))) <
CDate([RevisionDate])));
----

However it says on this page that you can use the FileDateTime
function in a query:
http://www.techonthenet.com/access/functions/file/filedatetime.php

I thought I might have something formatted incorrectly, so I have
tried simplifying the expression to just list the DateTime of the file
(as opposed to converting it to a Short Date format and comparing it
to the RevisionDate); but it still tells me that FileDateTime is an
undefined function.

So, can someone tell me if FileDateTime is a valid query function or
if it works only in VBA? And if not, is there another way to
accomplish what I'm trying to do? Thanks much.

 

[Jeff]

Well, I'm a bit over my head, but after some experimentation, I
created a module that defines a function to get the FileDateTime of
the documents - which I can then call from a query. When I run the
query, I now have the current Revision Date and the actual File Date
for each of the documents in the database.

Unfortunately, when I try to compare the two dates and get a report of
Documents where [File Date] < [Revision Date], the query won't run. It
first prompts me for a file name, then says the expression is
incorrect or is too complicated. I tried creating a second query
based on the first query, but that doesn't work either. So I'm back
to visually scanning the two columns of dates to look for errors. So
close and yet so far.

When I stop banging my head against the wall and find the patience to
study it again, maybe I will see if I convert the query to a
make-table query and create a new temporary table and then compare the
dates in the temporary table. Seems like a lot of trouble to do
something that should be relatively easy.

Okay, thanks! I didn't know you could define a function for a query. I
had thought it was an Access built-in function since it worked in VBA
on the form. Now I'll have to try to figure out how to do that.

You need to define the function FileDateTime
in a module, any module as long as it is public.

Short question: Will the FileDateTime function work in an Access
query? Or is there another way to read the Date Modified attribute of
a file from an Access query?

Long story: (descriptions and code are simplified with generic names
for clarity)
I'm trying to create a query to check whether all the documents in a
folder are up-to-date.

I have a database with a table of a large number of documents. The
table has a field "RevisionDate". The actual documents (in pdf
format) are in a folder that may be located anywhere (on the local
computer or on a server in another location). I created a custom
database property to hold the location of the Documents folder. To
open a document, I select the desired document from a listbox on a
form and concatenate the DocumentLocation and the DocumentName, e.g.:
----
Dim pdffile as string.

pdffile = [DocumentLocation] & "\" & [DocumentName]
Application.FollowHyperlink pdffile, , True
----
On the form, I can check the date of individual documents by reading
the Date Modified attribute of the file and comparing it to the
[RevisionDate] field.
----
pdfdate = Format(FileDateTime(pdffile), "Short Date")
If CDate(RevisionDate) > CDate(pdfdate) Then
MsgBox "Error - The selected document is outdated!"
End If
----
But I have to scroll through a long list to check each one, so I would
like to create a query to find all outdated documents. When I try to
use FileDateTime in the query, I get an error:
Undefined function "FileDateTime" in expression.
----
SELECT [Documents].[Record_ID], [Documents].[DocumentName],
[Documents].[RevisionDate], [DocumentLocation] & "\" & [DocumentName]
& ".pdf" AS PDFfile, CDate(Format(FileDateTime([pdffile]),"Short
Date")) AS pdfdate FROM [Documents] WHERE
(((CDate(Format(FileDateTime([pdffile]),"Short Date"))) <
CDate([RevisionDate])));
----

However it says on this page that you can use the FileDateTime
function in a query:
http://www.techonthenet.com/access/functions/file/filedatetime.php

I thought I might have something formatted incorrectly, so I have
tried simplifying the expression to just list the DateTime of the file
(as opposed to converting it to a Short Date format and comparing it
to the RevisionDate); but it still tells me that FileDateTime is an
undefined function.

So, can someone tell me if FileDateTime is a valid query function or
if it works only in VBA? And if not, is there another way to
accomplish what I'm trying to do? Thanks much.

 

[Douglas J Steele]

You cannot refer to aliased fields in your comparison: you must repeat the
calculations.

In other words, assuming your query is something like:

SELECT Field1, Field2, MyFunction(Field1) AS [File Date],
MyOtherFunction(Field1) AS [Revision Date
FROM MyTable

you cannot use a WHERE clause of

WHERE [File Date] < [Revision Date]

Instead, you must use

WHERE MyFunction(Field1) < MyOtherFunction(Field1)

(don't worry if that seems inefficient: Access will optimize the query
internally)

"Jeff" wrote in message
Well, I'm a bit over my head, but after some experimentation, I
created a module that defines a function to get the FileDateTime of
the documents - which I can then call from a query. When I run the
query, I now have the current Revision Date and the actual File Date
for each of the documents in the database.

Unfortunately, when I try to compare the two dates and get a report of
Documents where [File Date] < [Revision Date], the query won't run. It
first prompts me for a file name, then says the expression is
incorrect or is too complicated. I tried creating a second query
based on the first query, but that doesn't work either. So I'm back
to visually scanning the two columns of dates to look for errors. So
close and yet so far.

When I stop banging my head against the wall and find the patience to
study it again, maybe I will see if I convert the query to a
make-table query and create a new temporary table and then compare the
dates in the temporary table. Seems like a lot of trouble to do
something that should be relatively easy.

Okay, thanks! I didn't know you could define a function for a query. I
had thought it was an Access built-in function since it worked in VBA
on the form. Now I'll have to try to figure out how to do that.

You need to define the function FileDateTime
in a module, any module as long as it is public.

Short question: Will the FileDateTime function work in an Access
query? Or is there another way to read the Date Modified attribute of
a file from an Access query?

Long story: (descriptions and code are simplified with generic names
for clarity)
I'm trying to create a query to check whether all the documents in a
folder are up-to-date.

I have a database with a table of a large number of documents. The
table has a field "RevisionDate". The actual documents (in pdf
format) are in a folder that may be located anywhere (on the local
computer or on a server in another location). I created a custom
database property to hold the location of the Documents folder. To
open a document, I select the desired document from a listbox on a
form and concatenate the DocumentLocation and the DocumentName, e.g.:
----
Dim pdffile as string.

pdffile = [DocumentLocation] & "\" & [DocumentName]
Application.FollowHyperlink pdffile, , True
----
On the form, I can check the date of individual documents by reading
the Date Modified attribute of the file and comparing it to the
[RevisionDate] field.
----
pdfdate = Format(FileDateTime(pdffile), "Short Date")
If CDate(RevisionDate) > CDate(pdfdate) Then
MsgBox "Error - The selected document is outdated!"
End If
----
But I have to scroll through a long list to check each one, so I would
like to create a query to find all outdated documents. When I try to
use FileDateTime in the query, I get an error:
Undefined function "FileDateTime" in expression.
----
SELECT [Documents].[Record_ID], [Documents].[DocumentName],
[Documents].[RevisionDate], [DocumentLocation] & "\" & [DocumentName]
& ".pdf" AS PDFfile, CDate(Format(FileDateTime([pdffile]),"Short
Date")) AS pdfdate FROM [Documents] WHERE
(((CDate(Format(FileDateTime([pdffile]),"Short Date"))) <
CDate([RevisionDate])));
----

However it says on this page that you can use the FileDateTime
function in a query:
http://www.techonthenet.com/access/functions/file/filedatetime.php

I thought I might have something formatted incorrectly, so I have
tried simplifying the expression to just list the DateTime of the file
(as opposed to converting it to a Short Date format and comparing it
to the RevisionDate); but it still tells me that FileDateTime is an
undefined function.

So, can someone tell me if FileDateTime is a valid query function or
if it works only in VBA? And if not, is there another way to
accomplish what I'm trying to do? Thanks much.

..

 

[Jeff]

You cannot refer to aliased fields in your comparison: you must repeat the
calculations.

In other words, assuming your query is something like:

SELECT Field1, Field2, MyFunction(Field1) AS [File Date],
MyOtherFunction(Field1) AS [Revision Date
FROM MyTable

you cannot use a WHERE clause of

WHERE [File Date] < [Revision Date]

Instead, you must use

WHERE MyFunction(Field1) < MyOtherFunction(Field1)

(don't worry if that seems inefficient: Access will optimize the query
internally)

----------
Thanks for the help! That was PART of the problem. When I substitute
the calculations for the aliases, it no longer prompts me for a file
name. But it still gives the error: "...typed incorrectly or too
complex to be evaluated."

I wouldn't expect anyone to spend more time on this; the following
explanation is only in case anyone is curious... and again THANKS for
the info about not using aliases. That will help in the future.

Substituting the calculation for the alias gives the following
expression:
---
....WHERE (((CDate([Rev
Date]))>CDate(FileModDate([Forms]![FormAdmin]![txtDocumentLocation] &
"\" & [Category] & "\pdf\" & [Category] & "_" & [DocumentNumber] &
".pdf"))));
---
[Rev Date], [Category], & [DocumentNumber] are actual fields in the
table, not aliases. FileModDate is the name of the custom function.

The name of the file is not stored in the table; only the document
Category and Number. The document location is taken from a text field
on a form (which is calculated from a database custom property).
Under the document location folder are various subfolders for
different categories of documents.
Under the category folders are subfolders for PDF and MDI (Microsoft
Document Imaging) files.
Within the PDF & MDI subfolders, files are given unique names by
concatenating the Category & underscore & document number & file
extension.

Note - I tried a test to simplify the expression by entering the
literal text of the document location into the query in place of the
[Forms]![FormAdmin]![txtDocumentLocation] reference, and it gave the
same error.]

And all of this is just the first HALF of the total query. For each
PDF file, there is a corresponding MDI file in another subfolder. I
also need to get the modification dates of the MDI files and check
that the MDI file date is >= the PDF file date.

I think all this is too complex for one query. I did a test using a
make-table query to combine the Revision Date, PDF file date, & MDI
file date into a temporary table. That seems to work and will make it
easier to compare the dates.

 

[Douglas J Steele]

Try creating a function that accepts the date and path parameters and
returns a boolean value depending on the comparison and using that function
call as your condition.

"Jeff" wrote in message
On Fri, 9 Dec 2011 21:35:15 -0500, "Douglas J Steele"

I wouldn't expect anyone to spend more time on this; the following
explanation is only in case anyone is curious... and again THANKS for
the info about not using aliases. That will help in the future.

Substituting the calculation for the alias gives the following
expression:
---
....WHERE (((CDate([Rev
Date]))>CDate(FileModDate([Forms]![FormAdmin]![txtDocumentLocation] &
"\" & [Category] & "\pdf\" & [Category] & "_" & [DocumentNumber] &
".pdf"))));
---
[Rev Date], [Category], & [DocumentNumber] are actual fields in the
table, not aliases. FileModDate is the name of the custom function.

The name of the file is not stored in the table; only the document
Category and Number. The document location is taken from a text field
on a form (which is calculated from a database custom property).
Under the document location folder are various subfolders for
different categories of documents.
Under the category folders are subfolders for PDF and MDI (Microsoft
Document Imaging) files.
Within the PDF & MDI subfolders, files are given unique names by
concatenating the Category & underscore & document number & file
extension.

Note - I tried a test to simplify the expression by entering the
literal text of the document location into the query in place of the
[Forms]![FormAdmin]![txtDocumentLocation] reference, and it gave the
same error.]

And all of this is just the first HALF of the total query. For each
PDF file, there is a corresponding MDI file in another subfolder. I
also need to get the modification dates of the MDI files and check
that the MDI file date is >= the PDF file date.

I think all this is too complex for one query. I did a test using a
make-table query to combine the Revision Date, PDF file date, & MDI
date into a temporary table. That seems to work and will make it
easier to compare the dates.