What am i doing wrong? Dlookup

[RockOnRay]

This is the scenario of what i am trying to do with the database i am working on .

1. The user tries to import a file by pressing a command button.


2. The VBA code behind the button checks the "last modified" date of the file against a stored "last modified" date associated with that file. This stored date is in a table that lists the files and their most recent "modified" dates. If the file is newer, the file is imported. If the file is NOT newer, the user is informed that the current data is up to date.
If the file is newer:
3. The file's "last modified" is stored in a table.
4. The file data is imported.

What i have as of now is not working. When its performing a dlookup its giving me a runtime error 94 which is Invalid Use Of Null. I will post the code here and if anyone could help me out i would appreciate it. If you have any question please let me know. I will respond asap.

Private Sub ImportBtn_Click()

    Dim iFile1 As Date
    Dim iFile2 As Date
    Dim iFile3 As Date
    Dim iFile4 As Date
    Dim File1 As String
    Dim File2 As String
    Dim File3 As String
    Dim File4 As String
    'Dim strJobID As String
    
    DoCmd.SetWarnings False
    
    File1 = DateValue(GetFileDateTime("F:\ProsData\Warehouse\Databases\Rayhan\Projects\Excel\david\Permit 50 Pre Sort.xls"))
    File2 = DateValue(GetFileDateTime("F:\ProsData\Warehouse\Databases\Rayhan\Projects\Excel\david\Pre Sort ML.xls"))
    File3 = DateValue(GetFileDateTime("F:\ProsData\Warehouse\Databases\Rayhan\Projects\Excel\david\Pre Sort.xls"))
    File4 = DateValue(GetFileDateTime("F:\ProsData\Warehouse\Databases\Rayhan\Projects\Excel\david\WonW Pre Sort.xls"))
    
    'strJobID = DLookup("[JobTypeID]", "Q_xLDateQuery", "[Date Field] =' JobTypeID'")
    
    'If Not IsNull(DLookup("[Date Field]", "Q_xLDateQuery", "[Date Field] = #" & File1 & "# And [Job Type ID] = '" & strCarrier & "'")) Then
    
   ' Else
   'End If
    
    iFile1 = DLookup("[Date Field]", "Q_xLDateQuery", "[Date Field] =  #" & File1 & "#")
    iFile2 = DLookup("[Date Field]", "Q_xLDateQuery", "[Date Field] =  #" & File2 & "#")
    iFile3 = DLookup("[Date Field]", "Q_xLDateQuery", "[Date Field] =  #" & File3 & "#")
    iFile4 = DLookup("[Date Field]", "Q_xLDateQuery", "[Date Field] =  #" & File4 & "#")
    '____________________________________________________________________________
    If Not IsNull(iFile1) Then
        MsgBox "You have already transfered Permit 50 Pre Sort Excel File.", vbOKOnly
        
    Else
        DoCmd.RunMacro "Import_Permit50"
    End If
    '_____________________________________________________________________________
    If IsNull(iFile2) = False Then
        MsgBox "You have already transfered Pre Sort ML Excel File.", vbOKOnly
        
    Else
        DoCmd.RunMacro "Import_PreSortML"
    
    End If
    '_____________________________________________________________________________
        
    If IsNull(iFile3) = False Then
       MsgBox "You have already transfered Pre Sort Excel File.", vbOKOnly
    
    Else
        DoCmd.RunMacro "Import_PreSort"
    End If
    '______________________________________________________________________________
    
    If IsNull(iFile4) = False Then
        MsgBox "You have already transfered WonW Pre Sort Excel File.", vbOKOnly
    Else
            
        DoCmd.RunMacro "Import_WonW"
    End If
    '______________________________________________________________________________
DoCmd.OpenQuery "Apnd_XLerror"


DoCmd.SetWarnings True


    MsgBox "All the Excel Reports has been transfered", vbOKOnly

-----------------------------------------------------------------------------------------
Public Function GetFileDateTime(sFilePathAndName As String)
On Error GoTo Error_Handler


    GetFileDateTime = FileDateTime(sFilePathAndName)


Error_Handler_Exit:
    On Error Resume Next
    Exit Function


Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: GetFileDateTime" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit


End Function
End Sub

[pbaldy]

Well, a variable declared as Date can't take a Null, which is likely why you get an error. You're testing for Null later, so is it possible for the DLookup to return Null? If so, you want to change the data type of the variables to Variant.

[ssanfu]

In the code you posted, the last two lines are

End Function 
End Sub

You cannot have a Function nested inside a Sub.

Move the "End Sub" above
"Public Function GetFileDateTime(sFilePathAndName As String)"



I think there better methods the compare the dates.
One way might be (still not very good), is to wrap the DLookup in the NZ() function, then compare if iFile > File1

(UNTESTED - Air code)

    iFile1 = Nz(DLookup("[Date Field]", "Q_xLDateQuery", "[Date Field] =  #" & File1 & "#"), #1/1/1900#)
    '____________________________________________________________________________
    If Not iFile1 > File1 Then
        MsgBox "You have already transfered Permit 50 Pre Sort Excel File.", vbOKOnly
        
    Else
        DoCmd.RunMacro "Import_Permit50"
    End If

I just don't like using the DLookup() function for this..