Lookup of corresponding file names 3

[reg999]

I have a database of sales records. I keep invoice numbers in a table and scanned images of hand written invoices as .pdf files in a directory on the hard drive.

I would like to list all of my sales and have the list show whether there is an existing .pdf file that corresponds with each record.

What is the easiest way to go about this?

Table field name: InvNo
Records: 4200, 4201, 4202, etc.

Invoice Directory (folder) on C:
Files: 4200.pdf, 4201.pdf, 4202.pdf, etc.

Thank you,
Reg'

 

[PHV]

Perhaps with a UserDefinedFunction ?
In a standard code module:

Public ExistsScan(varInvNo) As Boolean
If Trim(varFileName & "") <> "" Then
  ExistsScan = (Dir("C:\Invoice Directory\" & varInvNo & ".pdf") <> "")
End If
End Function

And now the SQL code:

SELECT InvNo, ExistsScan([InvNo]) As [Scanned?]
FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[reg999]

I put the module code (with the correct directory) in a new database module, and named it 'ExistsScan', but I must have misunderstood.

When running the SQL, I get "Undefined function 'ExistsScan' in expression.

Sorry, for the elementary mistake.
Reg

 

[PHV]

A module can't have the same name as a procedure, so rename it.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[reg999]

I thought that might be the case. With a different name, I get "Compile error. in query expression 'ExistsScan([InvNo])'."

Is there anything else that I need to substitute in your expressions? I have substituted the Directory containing the files, the table name containing 'InvNo', and [Scanned?] with an arbitrary name 'Scn'.

 

[lespaul]

did you rename the module or the procedure?

If you renamed the procedure then your query needs to reference THAT name.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[reg999]

Renamed the Module to 'Fscan1'

Also, I'm getting an error on the 'varFileName'. Do I need to substitute something else there?

 

[lespaul]

yes, there is no varFileName - change that to varInvNo

 

[PHV]

OOps, sorry for the typo :~/

Public ExistsScan(varInvNo) As Boolean
If Trim([!]varInvNo[/!] & "") <> "" Then
  ExistsScan = (Dir("C:\Invoice Directory\" & varInvNo & ".pdf") <> "")
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[reg999]

This will work great! Thank you both for all of the assistance.
Reg'

 

[reg999]

I know that this is off topic, and not in keeping with the forum guidelines, but to PHV, you have generously helped me out of several 'binds', of which I have benefited in one form or another. I would be very pleased to have a nice single malt or bottle of 'Chateau something or other' purchased for you at a location of your choice.

Indebted,
Reg'

 

[PHV]

I suggest this:

http://www.tek-tips.com/supportus.cfm

 

[reg999]

Done.

Again, many thanks.
Reg'