Solved: Report filter

[Gorsoft]

Hi, I stumbled upon this site and hope someone can help me with a problem that has been taxing me for several days now.

I need to produce a report which will show information about
image files in a folder. In the on open event of the report, I am
filtering the report's data to show only those records which the
database indicates have an image on the hard disc. I am then using the
Len code to determine if that file exists on the hard disc - if it
does I want the flag in fldCoverImage2 to be updated to show "Yes".
The code looks like this:

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String, db As DAO.Database, strImageFilename As String
Dim strImagePathname As String, rs As DAO.Recordset, rsFiltered As DAO.Recordset

strSQL = "Select * From tblDiscography where tblDiscography.fldCIFilename Is Not Null;"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF
If Len(Dir("D:\Databases\EMM\03 CoverImages\" & rs!fldCIFileName & " ')")) > 0 Then
rs.Edit
rs!fldCoverImage2 = True
rs.Update
End If
rs.MoveNext
Loop
Set rs = Nothing
strSQL = ""
strSQL = "SELECT tblDiscography.fldDiscID,tblDiscography.fldCIFilename, tblDiscography.fldCoverImage2, tblDiscography.fldCIPath FROM tblDiscography WHERE (((tblDiscography.fldCoverImage2)=True));"
Reports![rptCoverImageReconciliationHDDv2].RecordSource = strSQL

End Sub


The report produced has no records in it whereas I am expecting some. I am not clear whether it is my SQL syntax that is wrong or whether I just can't use that "if" clause with the recordset.


Any help would be appreciated.
Gordon

[hansup]

Hi, I stumbled upon this site and hope someone can help me with a problem that has been taxing me for several days now.

I need to produce a report which will show information about
image files in a folder. In the on open event of the report, I am
filtering the report's data to show only those records which the
database indicates have an image on the hard disc. I am then using the
Len code to determine if that file exists on the hard disc - if it
does I want the flag in fldCoverImage2 to be updated to show "Yes".
The code looks like this:

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String, db As DAO.Database, strImageFilename As String
Dim strImagePathname As String, rs As DAO.Recordset, rsFiltered As DAO.Recordset

strSQL = "Select * From tblDiscography where tblDiscography.fldCIFilename Is Not Null;"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF
If Len(Dir("D:\Databases\EMM\03 CoverImages\" & rs!fldCIFileName & " ')")) > 0 Then
rs.Edit
rs!fldCoverImage2 = True
rs.Update
End If
rs.MoveNext
Loop
Set rs = Nothing
strSQL = ""
strSQL = "SELECT tblDiscography.fldDiscID,tblDiscography.fldCIFilename, tblDiscography.fldCoverImage2, tblDiscography.fldCIPath FROM tblDiscography WHERE (((tblDiscography.fldCoverImage2)=True));"
Reports![rptCoverImageReconciliationHDDv2].RecordSource = strSQL

End Sub

The report produced has no records in it whereas I am expecting some. I am not clear whether it is my SQL syntax that is wrong or whether I just can't use that "if" clause with the recordset. Try changing your If Len to this:

If Len(Dir("D:\Databases\EMM\03 CoverImages\" & rs!fldCIFileName)) > 0 Then

[Gorsoft]

Hey Hansup,

That did the trick - such a simple thing.
Thank you.

Gordon

[Gorsoft]

In my enthusiasm at seeing a result on this. I neglected to do a full check. When I put the revised code into my live database, I found that instead of producing no results as was the case originally, it is now producing too many.

Here is the revised code:


Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String, db As DAO.Database, strImageFilename As String
Dim strImagePathname As String, rs As DAO.Recordset, rsFiltered As DAO.Recordset

Set db = CurrentDb
DoCmd.SetWarnings False
strSQL = "UPDATE tblDiscography SET tblDiscography.fldCoverImage2 = False;"
db.Execute strSQL
strSQL = ""
strSQL = "Select * From tblDiscography"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF
If Len(Dir("H:\My Documents\Databases\EMM\03 CoverImages\" & rs!fldCIFileName)) > 0 Then
rs.Edit
rs!fldCoverImage2 = True
rs.Update
End If
rs.MoveNext
Loop
Set rs = Nothing
strSQL = ""
strSQL = "SELECT tblDiscography.fldDiscID,tblDiscography.fldCIFilename, tblDiscography.fldCoverImage2, "
strSQL = strSQL & "tblDiscography.fldCIPath FROM tblDiscography WHERE (((tblDiscography.fldCIFileName) Is Null)"
strSQL = strSQL & " AND ((tblDiscography.fldCoverImage2)=True))Order by tblDiscography.fldDiscID ASC;"
Reports![rptCoverImageReconciliationHDDv2].RecordSource = strSQL
DoCmd.SetWarnings True
End Sub


The code should update the fldCoverImage2 flag to "True" where an image file exists on the hard disc. (The image file on the hard disc has the same name as fldCIFilename). In fact it is setting the flag to "True" for every record in the recordset. Clearly the "If Len" clause is not working.

Help.:help

Gordon

[OBP]

Gordon, I don't think it would work, the Len function measures the length of a string, not if a File actually exists.
So Len of Dir("H:\My Documents\Databases\EMM\03 CoverImages\ is around 47 characters without the file name.
If you ask it to give you the len of your statement using
Msgbox Len(Dir("H:\My Documents\Databases\EMM\03 CoverImages\" & rs!fldCIFileName)

you will see what is being returned in your arguement and I suspect it will always be greater than 0.
I would be inclined to try the FileSize Property, but you will have to set a reference to FSO to use it though.
Alternatively you could use filesearch to dump the Files in that folder in to a Temporary table and then search that recordset for your File name.

Also I am concerned by your SQL, in particular the Where statement where you are using "tblDiscography.fldCIFileName) Is Null" i.e Blank, which I would have thought will return only those records without an image file.
I would have thought you would either use Is Not Null or better still your new field "fldCoverImage2" is equal to -1, i.e. ticked.

[Gorsoft]

Thanks for your response OBP. I need to correct you however. Using the If Len(Dir) command and using the full path to a file (including the filename itself) is a proven way of determining if a file exists. If the value returned = 0, it exists, if > 0, it does not. I am using this successfully elsewhere in my database so I figured the problem here had to be something to do with the SQL syntax.

Your comments got me thinking, so I tried an alternative approach to defining the field fldCFilename. This latter field i(a text field) is in fact the same as the field fldDiscID (a number field) but has zeros to pad it out. So I used fldDiscID in the IF LEN clause. See final code below (if anyone else is remotely interested):


Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String, db As DAO.Database, strImageFilename As String
Dim strImagePathname As String, rs As DAO.Recordset, rsFiltered As DAO.Recordset
Set db = CurrentDb
DoCmd.SetWarnings False
strSQL = "UPDATE tblDiscography SET tblDiscography.fldCoverImage2 = False;"
db.Execute strSQL
strSQL = ""
strSQL = "Select * From tblDiscography"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF
'Pad out the Disc ID with extra '0's and also add .jpg extension
Select Case Len(rs!fldDiscID)
Case Is = 1
strImageFilename = "000" & rs!fldDiscID & ".jpg"
Case Is = 2
strImageFilename = "00" & rs!fldDiscID & ".jpg"
Case Is = 3
strImageFilename = "0" & rs!fldDiscID & ".jpg"
Case Is = 4
strImageFilename = rs!fldDiscID & ".jpg"
End Select
If Len(Dir("H:\My Documents\Databases\EMM\03 CoverImages\" & strImageFilename)) > 0 Then
rs.Edit
rs!fldCoverImage2 = True
rs.Update
End If
rs.MoveNext
Loop
Set rs = Nothing
strSQL = ""
strSQL = "SELECT tblDiscography.fldDiscID,tblDiscography.fldCIFilename, tblDiscography.fldCoverImage2, "
strSQL = strSQL & "tblDiscography.fldCIPath FROM tblDiscography WHERE (((tblDiscography.fldCIFileName) Is Null)"
strSQL = strSQL & " AND ((tblDiscography.fldCoverImage2)=True))Order by tblDiscography.fldDiscID ASC;"
Reports![rptCoverImageReconciliationHDDv2].RecordSource = strSQL
DoCmd.SetWarnings True
End Sub


Ths worked and produced 8 records which I was able to verify as meeting the criteria of - an image exists on the hard disc, but there is no reference in the database to the fldCIFilename. It was those images that I wanted to weed out.

Problem solved. Thanks for making me think further.

Gordon

[OBP]

Gordon, thanks for that info.
I still don't see how "Is Null" works to list the records with images though.

[CreganTur]

The Len function isn't necessary to test if a file exists. If the Dir function doesn't find the specified filepath, it will return a null string value. So you could simplify to this, which will run the code blocked within the conditional as long as the Dir function does not return a null string.
If Dir("H:\My Documents\Databases\EMM\03 CoverImages\" & strImageFilename) <> "" Then

[hansup]

The Len function isn't necessary to test if a file exists. If the Dir function doesn't find the specified filepath, it will return a null string value. So you could simplify to this, which will run the code blocked within the conditional as long as the Dir function does not return a null string.
If Dir("H:\My Documents\Databases\EMM\03 CoverImages\" & strImageFilename) <> "" Then
If Len(Dir("your\pathspec\here")) > 0
executes significantly faster than
If Dir("your\pathspec\here") <> ""

It could be an especially useful optimization if he will be evaluating thousands of image records.

Hans

[Gorsoft]

Gordon, thanks for that info.
I still don't see how "Is Null" works to list the records with images though.

Sorry if I didn't make it clearer - the "Is null" code (in fact the whole report) is designed to find images on the hard disc that have no reference (ie fldCIPath) in the database.

By the way, the code I have seems pretty quick even with 2000+ images to search through.

Gordon