export .csv with filename dependent on date

[kchang]

I am not sure if this is even possible. All of my research has me leaning towards "no", but maybe I missed something. I am trying to export and save a .csv file whose name depends on the date entered. That is, I have a Microsoft Access program with a button that exports a .csv file. The button asks for a date and then gathers all of the information inputed on that day and exports it. Is there a way to code it such that it takes the date given by the user (ex. 9/1) and turns that into the filename (ex. 070901.csv)?

Also, would it be possible to have the file be csv without actually naming it .csv (i.e. .pos)?

[SammyB]

Should be possible & easy. Access doesn't care what you call the file:

DoCmd.TransferText acExportDelim, "delim", "tablename", "filename", False, "headersrequired"
which usually translates to
DoCmd.TransferText acExportDelim, "", "tablename", "filename", False, ""
where tablename is the name of your table and filename is the name of your file

[MMcCarthy]

I am not sure if this is even possible. All of my research has me leaning towards "no", but maybe I missed something. I am trying to export and save a .csv file whose name depends on the date entered. That is, I have a Microsoft Access program with a button that exports a .csv file. The button asks for a date and then gathers all of the information inputed on that day and exports it. Is there a way to code it such that it takes the date given by the user (ex. 9/1) and turns that into the filename (ex. 070901.csv)?

Also, would it be possible to have the file be csv without actually naming it .csv (i.e. .pos)?

All you have to do is create a variable to hold the filename. You don't say how you are getting it so I'm presuming an input box

[CODE=vb]
Dim filename As String
Dim path As String

path = "full directory path to the folder where the file will be placed"
' remember to end the path string with a \

filename = Format(InputBox ("Enter date:"), "dd/mm/yyyy")
filename = path & filename & ".pos"

' then use the code sammyB gave you

[/CODE]

You can name the file anything you like (e.g. .pos) but I can't guarantee how it will behave. Try it and see.

[VBPhilly]

I am not sure if this is even possible. All of my research has me leaning towards "no", but maybe I missed something. I am trying to export and save a .csv file whose name depends on the date entered. That is, I have a Microsoft Access program with a button that exports a .csv file. The button asks for a date and then gathers all of the information inputed on that day and exports it. Is there a way to code it such that it takes the date given by the user (ex. 9/1) and turns that into the filename (ex. 070901.csv)?

Also, would it be possible to have the file be csv without actually naming it .csv (i.e. .pos)?

Most .csv files ive seen are simply comma separated values (csv). In other words, you can open them with any text editor.
A good test is if you created a .csv, Excel defaults to opening it. If the format is bad, Excel will complain.
Not sure if this answers your question or not.

[kchang]

All you have to do is create a variable to hold the filename. You don't say how you are getting it so I'm presuming an input box

[CODE=vb]
Dim filename As String
Dim path As String

path = "full directory path to the folder where the file will be placed"
' remember to end the path string with a \

filename = Format(InputBox ("Enter date:"), "dd/mm/yyyy")
filename = path & filename & ".pos"

' then use the code sammyB gave you

[/CODE]

You can name the file anything you like (e.g. .pos) but I can't guarantee how it will behave. Try it and see.

Thanks, your suggestion worked for the most part. Access doesn't seem to like the file format I'm giving it (.pos), saying that it "Cannot update. Database or object is read-only." It works if I name it something conventional (i.e. .csv). Any thoughts?

As for how I got the information/filename here is my original code:
[CODE=vb]
Private Sub exportreport_Cl ick()
On Error GoTo Err_exportrepor t_Click

DoCmd.TransferT ext acExportDelim, Query, "ExportFile ", "Q:\Messengers\ postage_export. csv"

Exit_exportrepo rt_Click:
Exit Sub

Err_exportrepor t_Click:
MsgBox Err.Description
Resume Exit_exportrepo rt_Click

End Sub
[/CODE]
I basically hard coded the filename and used a query to prompt the user for a date and grab the information.

[MMcCarthy]

I've never tried to export a .pos file. Try exporting it as a .csv and then rename the file.

[SammyB]

Thanks, your suggestion worked for the most part. Access doesn't seem to like the file format I'm giving it (.pos), saying that it "Cannot update. Database or object is read-only." It works if I name it something conventional (i.e. .csv). Any thoughts?

As for how I got the information/filename here is my original code:
[CODE=vb]
Private Sub exportreport_Cl ick()
On Error GoTo Err_exportrepor t_Click

DoCmd.TransferT ext acExportDelim, Query, "ExportFile ", "Q:\Messengers\ postage_export. csv"

Exit_exportrepo rt_Click:
Exit Sub

Err_exportrepor t_Click:
MsgBox Err.Description
Resume Exit_exportrepo rt_Click

End Sub
[/CODE]
I basically hard coded the filename and used a query to prompt the user for a date and grab the information.

I think your parameters are wrong for line 4. See my post above. Did you open the file in the editor to see if it looked OK?

[kchang]

I think your parameters are wrong for line 4. See my post above. Did you open the file in the editor to see if it looked OK?

That's how I used to export the file. For all intents and purposes it seemed to work; however, the users want the file to be named based on the date. Your code combined with what mmccarthy gave me does the job.

I've never tried to export a .pos file. Try exporting it as a .csv and then rename the file.

Yeah, that's what I ended up doing.