MS Access: Dir Function
This MSAccess tutorial explains how to use the Access Dir function with syntax and examples.
Description
The Microsoft Access Dir function returns the first filename that matches the pathname and attributes specified. To retrieve additional filenames that match pathname and attributes, call Dir function again with no arguments.
Syntax
The syntax for the Dir function in MS Access is:
Dir [( path [, attributes ] ) ]
Parameters or Arguments
- path
- Optional. It the path to a file, folder, or directory. If the path is not found, the Dir function will return a zero-length string.
- attributes
Optional. It is the sum of the file attributes. File attributes can be one or a combination of the following values:
VB Constant Value Explanation vbNormal 0 Normal (Default) vbReadOnly 1 Read-only vbHidden 2 Hidden vbSystem 4 System file vbVolume 8 Volume label vbDirectory 16 Directory or folder vbAlias 64 File name is an alias
Note
You can use wildcard characters to specify multiple files. The patterns that you can choose from are:
Wildcard | Explanation |
---|---|
* | Allows you to match any string of any length (including zero length) |
? | Allows you to match on a single character |
If you wish to iterate over all files in a directory, use an empty string:
Dir ("")
Returns
The Dir function returns a string value.
Applies To
The Dir function can be used in the following versions of Microsoft Access:
- Access 2019, Access 2016, Access 2013, Access 2010, Access 2007, Access 2003, Access XP, Access 2000
Example
Let's look at how to use the Dir function in MS Access:
Dir("C:\example.mdb") Result: "example.mdb" Dir("C:\ex*.mdb") Result: "example.mdb" Dir("C:\exampl?.mdb") Result: "example.mdb"
Example in VBA Code
The Dir function can only be used in VBA code in Microsoft Access.
For example:
Dim LResult As String LResult = Dir("C:\example.mdb")
In this example, the variable called LResult would now contain the filename of the example.mdb file.
Frequently Asked Questions
Question: How can I use the Dir function to test whether a file exists?
Answer: This can be done with a formula that utilizes a combination of the Dir function, If function, and Len function.
For example:
If Len(Dir("c:\example.mdb")) = 0 Then Msgbox "This file does NOT exist." Else Msgbox "This file does exist." End If
Question: I'm not sure if a particular directory exists already. If it doesn't exist, I'd like to create it using VBA code. How can I do this?
Answer: You can test to see if a directory exists using the VBA code below:
If Len(Dir("c:\TOTN\Examples", vbDirectory)) = 0 Then MkDir "c:\TOTN\Examples" End If
In this example, the code would first check to see if the c:\TOTN\Examples directory exists. If it doesn't exist, the MkDir statement would create a new directory called Examples under the c:\TOTN directory.
Advertisements