# Access exporting text files with variable file names



## Mr. Plow

I have a music database that I am trying to export txt playlists from.  The code would cycle through a table of dates and date codes (example:  7/1/1967; 19670701) to create a playlist with absolute file addresses.  The problem I get when running isn't with the queries, but with the filename.  My code is as follows:



		VBA Code:
__


Sub rundata()


Dim rs As DAO.Recordset, i As Long
Dim dt As String


Set rs = CurrentDb.OpenRecordset("pdates")

Do While Not rs.EOF
    'DoCmd.RunMacro "macro1"
    dt = "D:\music\Playlists\Charts\" & DLookup("[DtStr]", "[Chart Date]") & ".txt"
    DoCmd.TransferText transferType:=acExportDelim, TableName:="Playlist 2", FileName:=dt, hasfieldnames:=True
    DoCmd.OpenQuery "Delete Min Date"
    'rs.MoveNext
Loop
MsgBox "Done"
Set rs = Nothing

End Sub


The error I get from this is  Run-time error '3125':  '19670701.txt' is not a valid name.  Make sure that it does not include invalid characters of punctuation and that it is not too long."


----------



## welshgasman

TBH I cannot see anything wrong with the syntax. 
However why the code, when you can get that from a format() function of your date field? Plus you are always picking up the first date, no criteria selected?
Is 19670701 actually a text field?

Perhaps try CStr() ?


----------



## Mr. Plow

19670701 is a text field.  It is being pulled from the [DtStr] field in the dlookup.  

I tried a cstr(), but it put in a / instead of a -, which I can't use in file name.


----------



## welshgasman

I cannot see how Cstr() would do that? 

Try using Format() on your actual date field
Format(YourDateField,"yyyymmdd")

Also check the Len() of DtStr in case of any hidden characters.


----------



## Mr. Plow

Ooooh.  This did it!  Ran a Trim$() on the DtStr field and it worked.  Must have had a blank space at the start of the string.  Thanks!


----------



## Micron

You realize that with no criteria in the DLookup, you will get a random record from the table/query (usually the 1st in my experience)? That might be OK in your situation but maybe worth mentioning...


----------

