Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    31

    Unanswered: Dir command in Query

    I have a 2003 application that in one the queries uses the DIR command. Within this query I am reporting back as to whether a file exists. The actual field within the query is IIf(dir(filevariable)>" ","found","not found")

    This has worked properly in 2003.

    When the application is put in 2007 it errors out on this query. I have tried modifing the query and changing the file variable to a known file and still the query gets the following message...Undefined function 'Dir' in expression....clicking help brings up error 3085.

    I've tried reloading references to no avail.

    I have written a mini function using the DIR command and that has been successful but I'd really like to have the query work like it does in 2003.

    Any help would be appreciated.

    thanks

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by IMAcoolClassic View Post
    I have a 2003 application that in one the queries uses the DIR command. Within this query I am reporting back as to whether a file exists. The actual field within the query is IIf(dir(filevariable)>" ","found","not found")

    This has worked properly in 2003.

    When the application is put in 2007 it errors out on this query. I have tried modifing the query and changing the file variable to a known file and still the query gets the following message...Undefined function 'Dir' in expression....clicking help brings up error 3085.

    I've tried reloading references to no avail.

    I have written a mini function using the DIR command and that has been successful but I'd really like to have the query work like it does in 2003.

    Any help would be appreciated.

    thanks

    I have never tried to use the DIR command inside a SQL statement like that. I would normally have used a function. That is my standard mode of operations.

    I am curious. I will give Access 2007 and 2010 a test to see what happens.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    May 2007
    Posts
    31
    wondering whether you've had an opportunity to preform the above tests. If so what have you discovered.

    Also, I am curious as to how I would create a function to be called from a query.....could you steer me in a direction?

    Thanks!

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by IMAcoolClassic View Post
    wondering whether you've had an opportunity to preform the above tests. If so what have you discovered.

    Also, I am curious as to how I would create a function to be called from a query.....could you steer me in a direction?

    Thanks!
    I got an error in Access 2007 and 2010 using your example in a query.


    Here is how I would do this with a function.

    Place this code in a standard code module:

    Code:
    Public Function fDoesFileExist(pstrPath) As String
    
    
        If Dir(pstrPath) > "" Then
          fDoesFileExist = "found"
        Else
          fDoesFileExist = "not found"
        End If
    
    End Function


    To use in your query:

    fDoesFileExist([filevariable])
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    May 2007
    Posts
    31
    Thank you so much for your guidance - we are back to operational!

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by IMAcoolClassic View Post
    Thank you so much for your guidance - we are back to operational!
    You're welcome!

    Glad we could assist.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •