Dlookup with filepath as criteria (1 Viewer)

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Hi All,

I've changed the way we store our quotations in our quoting database.
Where before we would store them under the project number, we are now going to sore them under the client name first and then have a subfolder for the various projects.

In order to move over all the old quotations to the new style, I'm trying to loop through all the files in the storage folder and then get the QuotationID , so that I can use fso to move them and update the QuotationLocation field in the table.

Everything seems to be going swimmingly, except it doesn't accept the filepath and filename as the criteria.


This is a snippet of my code:
Code:
Dim strCriteria As String

For Each File In SubFolder.Files
    strCriteria = "[QuotationLocation] = '" & File & "'"

    intQuote = Nz(DLookup("[QuotationID]", "tblQuotation", strCriteria), 0)

    Debug.Print intQuote

Next

This is giving me output in the immediate window of:
0
Where it should have the QuotationID as an integer.

An example of the info in QuotationLocation is:
O:\SignNET\JobRelated\31689\20170208 Palmer Equestrian.pdf

When I debug.print strCriteria on this line I get:
[QuotationLocation] = 'O:\SignNET\JobRelated\31689\20170208 Palmer Equestrian.pdf'

When I manually search the table I can find the item, but not through the Dlookup.

Am I missing a quotation mark somewhere?
Can anyone see an issue? Would love some help here.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:25
Joined
May 7, 2009
Messages
19,169
For Each File In SubFolder.Files
strCriteria = "[QuotationLocation] = '" & File.Path & "'"
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Hi Arnel,

Have tried that but still getting NULL (0) from the Dlookup


Sent from my iPhone using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 08:25
Joined
Jan 14, 2017
Messages
18,186
Unlikely to solve your issue but I suggest using a long variable lngQuote as at some point you will exceed the integer limit of 32767.

Also the two sets of [] are superfluous … though won't do any harm
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Will definitely have to convert to long eventually. Which is on my list of todos.

Have tried this as a long too though but still doesn’t work.


Sent from my iPhone using Tapatalk
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:25
Joined
Apr 27, 2015
Messages
6,286
Will the DLookUp work if you use the actual String (path name) instead of the variable?
 

isladogs

MVP / VIP
Local time
Today, 08:25
Joined
Jan 14, 2017
Messages
18,186
Also does it work if there are no spaces in the file path?
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Hi NauticalGent,

Have just tried:
Code:
intQuote = Nz(DLookup("[QuotationID]", "tblQuotation", "[QuotationLocation] = 'O:\SignNET\JobRelated\31689\20170208 Palmer Equestrian.pdf'"), 0)

Still getting a NULL (0) return value for intQuote
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Also does it work if there are no spaces in the file path?

Isladogs, will have to make a copy of a PDF in one of the folders and create a dummy record in the table to test this.

Bear with me.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:25
Joined
Sep 21, 2011
Messages
14,050
You don't need to create a file. Just put a path to a known file in the table.
I take it you have inspected the strcriteria?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:25
Joined
Apr 27, 2015
Messages
6,286
Is the "O" drive virtual? I pretty sure it is. If so try "\\DS\SignNET\JobRelated\31689\20170208 Palmer Equestrian.pdf" (UNC format).
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Hi guys,

Had to create a file, as the For loop runs through the actual folders, so needed it to pickup a file that had no spaces so that it could compare it to the table.

Still no joy...

Have definitely debugged the strCriteria
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Is the "O" drive virtual? I pretty sure it is. If so try "\\DS\SignNET\JobRelated\31689\20170208 Palmer Equestrian.pdf" (UNC format).

The O drive is a network drive. The code I have which saves the files (reports saved to PDF) in the folders works, along with all other code which references the network drive as "O:"
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Here is my entire code block:

Please be kind, I haven't had time to refactor, just trying to get a working concept first:
Code:
Option Compare Database
Option Explicit

Public Sub MoveQuotations()

Dim FileSystem As Object
Dim HostFolder As String
Dim Folder
Dim SubFolder

HostFolder = "O:\SignNET\JobRelated"

Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)

End Sub

Sub DoFolder(Folder)
    Dim SubFolder
    Dim JobNumber
    Dim strClient As String
    Dim intClient As Integer
    Dim myOldPath As String
    Dim myNewPath As String
    Dim myExt As String
    Dim myFile As String
    Dim intCount As Integer
    Dim intQuote As Integer
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    intCount = 0
    
    For Each SubFolder In Folder.SubFolders
    
        JobNumber = Split(SubFolder, "\")
        JobNumber = JobNumber(UBound(JobNumber))
        
        If IsNumeric(JobNumber) And intCount < 4 Then
        
            Dim File
            
            For Each File In SubFolder.Files
            
                myExt = fso.GetExtensionName(File)
                
                If myExt = "pdf" Then
        
                    intClient = DLookup("JobClient_FK", "tblJob", "[JobID] = " & JobNumber)
                    strClient = DLookup("ClientName", "tblClient", "[ClientID] = " & intClient)
                    
                    'Trying to get the quotation ID
                    Dim strCriteria As String
                    strCriteria = "[QuotationLocation] = 'O:\SignNET\JobRelated\30146\20170704HawkinsGroupLtd.pdf'"
                    'Debug.Print strCriteria
                    
                    intQuote = Nz(DLookup("[QuotationID]", "tblQuotation", strCriteria), 0)
                    
                    'myOldPath = fso.GetParentFolderName(File) & "\"
                    'myNewPath = fso.GetParentFolderName(SubFolder) & "\" & strClient & "\"
                    'myFile = fso.GetFileName(File)

                    Debug.Print File & " : " & intQuote & " : " & strCriteria
                    
                End If
    
                intCount = intCount + 1
            
            Next
        
        End If
        
    Next

End Sub

Note: I have introduced an intCount on the loop as there are around 3500 PDF files/quotations in the folders. So wanting to keep the output minimal.
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
There can often be multiple quotes/pdfs in each job/project folder, as we often provide multiple variations on a single project. Each one is linked back to the job through a field called JobID_FK.

Hence, why I need to move them individually.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:25
Joined
Sep 21, 2011
Messages
14,050
Hi guys,

Had to create a file, as the For loop runs through the actual folders, so needed it to pickup a file that had no spaces so that it could compare it to the table.

Still no joy...

Have definitely debugged the strCriteria

I would not even be looking at that code for now.
I would manually be getting a filename that is in the folder and in the table and just using the dlookup in the debug window. Getting to the bottom of why it cannot be found. Once that is solved, you are almost there.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:25
Joined
Sep 21, 2011
Messages
14,050
It appears spaces in the filename causes Null ?
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Okay. Been doing some tests in the immediate window using another field in the same table. I have a field in there called QuotationDescription and the first record has a value of "Quote rev1"

When I type in:
Code:
?DLookup("[QuotationID]", "tblQuotation", "[QuotationDescription] = 'Quote rev1'")

I get a result.

BUT... when I change that value to "Quote\rev1" and try

Code:
?DLookup("[QuotationID]", "tblQuotation", "[QuotationDescription] = 'Quote\rev1'")

I get Null.

So it appears to be the backslash.

Any thoughts on escaping this....
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
Sorted it!!!!

When I run
Code:
?DLookup("[QuotationID]", "tblQuotation", "[QuotationDescription] = 'Quote\\rev1'")

I get the correct result. A double slash to escape!
 

nhorton79

Registered User.
Local time
Today, 21:25
Joined
Aug 17, 2015
Messages
147
So what I did was created a tempFile variable rather than overwriting my File object variable.

Then I used:
Code:
tempFile = Replace(File, "\", "\\") ' replace single backslash with double slashes

Now getting my primary key back...Perfect!

Thanks Gasman and all for helping out.
I think it was that suggestion to stop looking at the code and try dlookups in the immediate window that got me across the line as I was able to experiment with greater flexibility.
 

Users who are viewing this thread

Top Bottom