By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,270 Members | 1,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,270 IT Pros & Developers. It's quick & easy.

Exporting MS Access table to text file and appending current date to file name.

P: 5
I have a table that I need to export out to a text fiel, without column headers. I also need to have the file name include currernt date. Can anyone help me? I'm using the following code:


[b]Public Function CreateFiles()

Dim db As Database
Dim rst As Recordset
Dim m_str As String
Dim dt_str As String
Dim y_str As String
Dim d As Variant
Dim dir_str As String
Dim loc As String, fl As String, I As Integer





d = FreeFile

m_str = Format(InputBox("Specify Month", , Month(Date)), "00")
If m_str = "" Then
Exit Function
End If

dt_str = Format(InputBox("Specify Day", , Day(Date)), "00")
If dt_str = "" Then
Exit Function
End If


y_str = InputBox("Specify Year", , Year(Date))
If y_str = "" Then
Exit Function
End If
Set db = CurrentDb



For I = Len(db.Name) To 1 Step -1
If Mid(db.Name, I, 1) = "\" Then
Exit For
End If
Next I
loc = Left(db.Name, I) & "Batches\Pmts_by_Checks_"



Set rst = db.OpenRecordset("PMTS_BY_Checks", dbOpenSnapshot)

Open loc & "PMTS_BY_Checks_" & m_str & dt_str & Right(y_str, 2) & ".txt" For Output As d
Do

Print #d, [AccountNumber] & "," & !Amount & "," & !PMTType & "," & ![PaymentDate] & "," & ![Description] & "," & "," & ![InvoiceNum] & "," & ![Check #]
.MoveNext
Loop While Not .EOF

Close d


End Function


I get the following error message:

Comile Error

External name not defined

It looks like error near Print #D, [AccountNumber]


Thank you in advance.
Apr 11 '07 #1
Share this Question
Share on Google+
2 Replies


pks00
Expert 100+
P: 280
How about something simple like this

DoCmd.TransferText acExportDelim, , "PMTS_BY_Checks", "c:\tsdn\Pmts_by_Checks_" & Format(Now(), "yyyymmdd") & ".txt", False

this exports to a folder called c:\tsdn
datetimestamp is YYYYMMDD

eg

filename is Pmts_by_checks_20070411
Apr 11 '07 #2

P: 5
Thank you. It worked but the only thing I do not want is quotes around each text field.

How about something simple like this

DoCmd.TransferText acExportDelim, , "PMTS_BY_Checks", "c:\tsdn\Pmts_by_Checks_" & Format(Now(), "yyyymmdd") & ".txt", False

this exports to a folder called c:\tsdn
datetimestamp is YYYYMMDD

eg

filename is Pmts_by_checks_20070411
Apr 12 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.