Generate File Name for Txt Transfer

[mriggio]

I am working on a database with numerous records. I would like to implement code where, using a Transfer Txt, the txt.file name is automatically generated. So for January, the .txt file is automatically transferred and named as "jan" or "1" in the specified folder, then for February, then March... is this possible?

Thank you.

 

[Russell Hauf]

It is definitely possible. Can you post a little of what you have now? When you say, "So if it is January...", what do you mean by "it"?

Thx,

rh

 

[mriggio]

I have a ton of records and i would like to program a code that, on the first of each month "archives" the previous month as a text file in a specified folder, and automatically names the file with the previous month's name or some other sequential name. Does that help?

Thanks.

 

[Russell Hauf]

Well, you can get the previous month something like this:

    Dim strPrevMon as String

    strPrevMon = Format(DateSerial(Year(Now),Month(Now),0),"mmmm")

HTH,

Russell

 

[mriggio]

So, I could use that code to get the name of the previous month, correct? How would I use that information to query my records for all records corresponding to that month, then transfer that info to a .txt file and save it by the name I got in the string?

Thanks.

 

[Russell Hauf]

Depends on how your data is stored. In your query, you can use basically the same thing, something like:

Select blah blah blah from MyTable WHERE
Month([MyDateField]) = Month(DateSerial(Year(Now),Month(Now),0)) AND
Year([MyDateField]) = Year(DateSerial(Year(Now),Month(Now),0))
...

The TransferText method has an argument called filename that can be used to name the file you are exporting to. Look up TransferText in VB help for more info.

You could also make a function like this (in a regular module) and then use it both in your query and in your TransferText call.

Public Function LastMonth() as Date
    LastMonth = DateSerial(Year(Now),Month(Now),0)
End Function

You would use it like this:

Select blah blah blah from MyTable WHERE
Month([MyDateField]) = Month(LastMonth) AND
Year([MyDateField]) = Year(LastMonth)
...

in your query, and something like this in your TransferText call:

Docmd.TransferText acExportDelim, , "MyQuery", _
    Format(Month(LastMonth),"mmmm") & ".txt", True

(the important parts being "MyQuery", and the Format(Month(LastMonth),"mmmm") & ".txt" parts.

HTH,

Russell

 

[Russell Hauf]

One slight change if you use the LastMonth function in your query. It needs to be like below -- instead of LastMonth, use LastMonth().

Select blah blah blah from MyTable WHERE
Month([MyDateField]) = Month(LastMonth()) AND
Year([MyDateField]) = Year(LastMonth())