the Manx Man's MS Access Pages
to subscribe - click here

Incremental File Names (020)

NOTE:This article has been converted to our new web site. See it here.

A recent project for a client required a routine to export data to a text file format. The client wanted the first "batch" of each month to be named Ayyyymm.txt where the yyyymm was the current year and month. The second one in that month would be Byyyymm.txt, and so on. They had never gone past "K" in any one month and wanted to stay with the same naming style. The old manual system caused problems every 2 or 3 months because someone would give 2 batches in a row the same name.

Here is a simple version of the code I came up with. I'll leave it to you to add error checking and other embellishments.

------------------------
Function NextFileName()

    Dim fileprefix As String*1
    Dim tempNextFileName As String
    Dim txtFilePath As String
    Dim strYYYYMM as String*6
   
    strYYYYMM = Format(Now(),"yyyymm")
    txtFilePath = "c:\exported_data\"
    fileprefix = "A"

    Do While True
        tempNextFileName = txtFilePath & fileprefix & _
                       strYYYYMM & ".txt"
        If Dir(tempNextFileName) = "" Then
            Exit Do
        End If
        fileprefix = Chr(Asc(fileprefix) + 1)
    Loop

    NextFileName = tempNextFileName

End Function
------------------------

The keys to the routine are the use of the Dir() function to check for an existing file, and the use of the Chr(Asc()) combination to increment the prefix character.  (not to mention the use of the Format function to created the yyyymm string)