Here's a weird request I just got. I need to program Access to create
text files in a specific folder and put on extensions that are like .
000, the next one is .001, etc. How in the world would I even start
something like that?

Thanks,
Laura

musicloverlch wrote:

I suppose you could read every file's extension and take the largest.
You'd be better off having a table that holds the current extension
value. Take that value, inc it by 1, write back that value to the table
and use format(value,"000") to zero pad the result. What happens after 999?

To create the files...look at TransferText in help. Or to roll your own
look at Open,Close, Input, Write, Print, Put and their examples in help.
Also Dir to find files.

Or I forgot to mention that it resets every day. Files are picked up
daily from another company.

On Apr 24, 10:08*am, Salad <o... (AT) vinegar (DOT) com> wrote:

musicloverlch wrote:

and set the counter field to 0. Then increment.

It you store a table with the documents, you could split the name and
extension and get the max extension number for that day as well.

Cool! Thanks!

On Apr 24, 12:55*pm, Salad <o... (AT) vinegar (DOT) com> wrote:

Here's a couple of functions that I have written and use frequently in
databases to provide the suffices you want. They may need to be
modified for your circumstances.

Public Function getNextDocumentName(filePrefix As String, fileBaseName
As String, folderName As String) As String
' a fileName will be of the form 9999_03.doc
Dim docName As String
docName = filePrefix & fileBaseName

Dim fSearch As FileSearch
With FileSearch
.LookIn = folderName
.FileName = docName & "_*.doc"
Dim highestNumber As Integer
highestNumber = 0
If .Execute > 0 Then
Dim index As Integer
For index = 1 To .FoundFiles.Count
Dim thisFileName
thisFileName = Mid(.FoundFiles(index), Len(.LookIn) +
2)

Dim letterNumber As Integer
letterNumber = getLetterNumber(thisFileName)

If letterNumber > highestNumber Then
highestNumber = letterNumber
End If
Next index
End If
End With
docName = docName & "_" & Format(highestNumber + 1, "00")

getNextDocumentName = docName
End Function
Public Function getLetterNumber(FileName) As Integer
Dim underscore
underscore = InStrRev(FileName, "_") ' find rightmost
"_"
Dim letterNumber
letterNumber = Mid(FileName, underscore + 1) ' select the
number to the right e.g. 03.doc

getLetterNumber = CInt(Left(letterNumber, Len(letterNumber) -
4)) ' strip .doc

End Function

On Apr 24, 10:33 am, musicloverlch <lho... (AT) gmail (DOT) com> wrote:
I've been down that exact road. Rather than using the existing file
names in the folder to determine the next extension, it is much better
to store the file names in the database and use that table (with
perhaps a separate Extension field) to determine the next extension to
use. Users will have to delete the record/row using the database
rather than just deleting the file from the folder, but it will be
well worth the extra effort. I have seen situations where using file
names in a directory on a network like a database table, even when
using the Indexing Service, can cause Access to slow down seriously,
on the order of five to 15 seconds just calculating the next
extension, once the directory got up to about 4000 or 5000 files.
Even using subfolders in that directory would eventually cause
problems because you'd need to determine if the subdirectory exists.
I'll be rewriting about four databases soon to do what I suggested.

I hope this helps.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Most people make their biggest mistakes during the first step.