Filter records on date & time atrributes of in table stored filena

[Patrick]

I have a table where for each record 2 filenames (including path) are stored.
I made a form witch shows the file date&time of the 2 files
<< ControlSource: =FileDateTime([filename1]) >>
and determines if filename2 is older than filename1.
<<ControlSource:
=IIf(FileDateTime([filename1])<=FileDateTime([filename2]);"Up to Date";"OUT
OF DATE!!!") >>
This works fine. The form shows all records showing if up to or out of date.

I cannot filter the form on this upto/outof-date field to show only
out-of-date records.

Can I make a query or filter witch only shows records that are "OUT OF
DATE!!!" ?

Thanks

 

[John Spencer]

It would be slow but in a queryyou should be able to use a calculated field like:

Field: OutOfDate: FileDateTime([filename1])<=FileDateTime([filename2])
Criteria: True

If you have a lot of records the performance on this may be unacceptable.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Patrick]

The field OutOfDate is filled with a 0(up-to-date) or a -1(out-of-date) when
I do not use a criteria.
When I use the criteria True or -1 I get an error "Data type mismatch in
criteria expression"

Same error with calculated field:
OutOfDate:
IIf(FileDateTime([filename1])<=FileDateTime([filename2]);"UP";"OUT")
criteria "OUT"
--
Patrick

It would be slow but in a queryyou should be able to use a calculated field like:

Field: OutOfDate: FileDateTime([filename1])<=FileDateTime([filename2])
Criteria: True

If you have a lot of records the performance on this may be unacceptable.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a table where for each record 2 filenames (including path) are stored.
I made a form witch shows the file date&time of the 2 files
<< ControlSource: =FileDateTime([filename1]) >>
and determines if filename2 is older than filename1.
<<ControlSource:
=IIf(FileDateTime([filename1])<=FileDateTime([filename2]);"Up to Date";"OUT
OF DATE!!!") >>
This works fine. The form shows all records showing if up to or out of date.

I cannot filter the form on this upto/outof-date field to show only
out-of-date records.

Can I make a query or filter witch only shows records that are "OUT OF
DATE!!!" ?

Thanks

 

[John Spencer]

Any nulls or non-existent file names will cause an error to be generated by
FileDateTime. In order to handle that you might consider writing a custom vba
function to test FileName1 and fileName 2

UNTESTED AIR CODE FOLLOWS
Public Function fFileCompare(f1, f2)

If len(F1 & "") = 0 then
fFileCompare = "N/A"
Elseif Len(F2 & "") = 0 Then
fFileCompare = "N/A"
ElseIf Len(Dir(F1)) = 0 then
fFileCompare = "N/A"
ElseIf Len(Dir(F2) = 0 Then
fFileCompare = "N/A"
Else
IF FileDateTime([filename1])<=FileDateTime([filename2]) Then
fFileCompare = "Up"
Else
fFileCompare = "Down"
End if


End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Patrick]

John,

Thanks for the info.
I didn't know how to implement a vba function in a query but your idea made
me look at my formula and I have it now, perhaps not the most nice/efficient
way but it works.

OutOfDate:
IIf([filename1]<>"";IIf([filename2]<>"";IIf(Dir([filename1])<>"";IIf(Dir([filename2])<>"";IIf(FileDateTime([filename1])<=FileDateTime([filename2]);"Up
to Date";"OUT OF DATE");"N/A");"N/A");"N/A");"N/A")

The processing time is more than acceptable. approx 350 filenames in < 2 sec.

--
Patrick

Any nulls or non-existent file names will cause an error to be generated by
FileDateTime. In order to handle that you might consider writing a custom vba
function to test FileName1 and fileName 2

UNTESTED AIR CODE FOLLOWS
Public Function fFileCompare(f1, f2)

If len(F1 & "") = 0 then
fFileCompare = "N/A"
Elseif Len(F2 & "") = 0 Then
fFileCompare = "N/A"
ElseIf Len(Dir(F1)) = 0 then
fFileCompare = "N/A"
ElseIf Len(Dir(F2) = 0 Then
fFileCompare = "N/A"
Else
IF FileDateTime([filename1])<=FileDateTime([filename2]) Then
fFileCompare = "Up"
Else
fFileCompare = "Down"
End if


End Function

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

The field OutOfDate is filled with a 0(up-to-date) or a -1(out-of-date) when
I do not use a criteria.
When I use the criteria True or -1 I get an error "Data type mismatch in
criteria expression"

Same error with calculated field:
OutOfDate:
IIf(FileDateTime([filename1])<=FileDateTime([filename2]);"UP";"OUT")
criteria "OUT"