TransferText--Want to save file as date

[wwiSports]

I am trying to autoate saving a comma delimited file using the TransferText Method.

This is my ultimate goal:

-Press the ExportConfirmation button
-A Inputbox appears asking for the file name
-I will enter ConfLtrDt and then the Month and Year
-And the file will save to:

G:\OE Book\Fulfillment\Confirmation\ **filename i entered in the input box**

I have the following code--but it's not working:

Private Sub ExportConfLtr_Click()

Dim FileName As String

FileName = Trim(InputBox("Enter a File Name", "File Name&quot)
If FileName = "" Then
If vbYes = MsgBox("Do you want to Cancel?", vbYesNo + vbQuestion + vbDefaultButton2, "Cancel?&quot Then
FileName = "CANCEL"
End If
End If

DoCmd.TransferText acExportDelim, , "ConfirmationLetter", "G:\OE Book\Fulfillment\Confirmation\ConfLtr & Date & .doc"



End Sub

Where am I going wrong??

Beth

 

[JPeters]

Try this, friend.

DoCmd.TransferText acExportDelim, , "ConfirmationLetter", "G:\OE Book\Fulfillment\Confirmation\ConfLtr" & Date$ & ".doc"

Date needs to be outside of the quotes, otherwise it will not perform the function, it will recognize it as text. And also, the Date() function outputs the date with slashes.. ##/##/### .. you need to change it to Date$, which uses hyphens instead. These will save as valid filenames.
An idea might be to always test values in a message box... I constantly throw stuff into message boxes to see if they output the way I want them to.

This should help!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[wwiSports]

It worked!!!!! Thank you very much Josh!!!
Beth

 

[JPeters]

You are welcome! Hoooray for tek-tips! ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------