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:

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...