User defined export file name

[Guest]

I need to export a daily report to a snapshot file, and either have the file
name include the date of the report (which is stored in a table and is not
the current date) or have the used be able to edit the file name before it is
saved. I've written code to add a date to the exported file name. My
problem is the report date is not always the current date or the same number
of days before the current date so I can't use Date() or something like
Date()-2. Here is my code. Any ideas of what I can do?

Function Output_reconciliation_report()
On Error GoTo Output_reconciliation_report_Err

DoCmd.OutputTo acReport, "RECONCILING REPORT", "SnapshotFormat(*.snp)",
"C:\2006\Reports\" & "Reconciling Report " & Month(Date) & "-" & Day(Date) &
"-" & Year(Date) & ".snp", False, "", 0

Output_reconciliation_report_Exit:
Exit Function

Output_reconciliation_report_Err:
MsgBox Error$
Resume Output_reconciliation_report_Exit

End Function

 

[Douglas J Steele]

Use DLookup to retrieve that stored date value from the table and format it
for your filename:

Dim dtmReportDate As Date

dtmReportDate = Nz(DLookup("ReportDate", "MyTable"), Date())
DoCmd.OutputTo acReport, "RECONCILING REPORT", "SnapshotFormat(*.snp)",
"C:\2006\Reports\" & "Reconciling Report " & Format(dtmReportDate),
"mm\-dd\-yyyy") & ".snp", False, "", 0


This assumes that your date is stored as the only row in the table. If
there's some criteria you have to use to retrieve it, you'd have to change
that DLookup statement.

 

[Ron2006]

If there is a field within the reporting data that will always have the
date of the report (either only date or earliest date or latest date)
create a query of that reporting data that gets top1 and do a dlookup
on that query getting the date

rptDate = dLookup ("[Reportdatefieldname]", "Report date query")

then

DoCmd.OutputTo acReport, "RECONCILING REPORT", "SnapshotFormat(*.snp)",

"C:\2006\Reports\" & "Reconciling Report " & Month(rptDate) & "-" &
Day(rptDate) &
"-" & Year(rptDate) & ".snp", False, "", 0

I would suggest the following change since with the above format you
will NOT be able to tell the difference between a report for January
11, 2006 and November 1, 2006.

rptName = "C:\"& Year(rptDate) & \Reports\" & "Reconciling Report " &
Year(rptDate) ")&
"-" & format(Month(rptDate),"00") & "-" & format(Day(rptDate),"00") &
".snp"

DoCmd.OutputTo acReport, "RECONCILING REPORT", "SnapshotFormat(*.snp)",

rptName, False, "", 0

Even if you do not put the year in front, at least with the formating
added you can differentiat the files I mentioned.

And the year portion of the directory should be self modifying or you
will hve to change it every year and then you have to decide how to
handle reports in January for December of the previous year, etc. which
will get really tough if it is hardcoded.

Also in general I have taken to creating a control table in all my apps
that contain the directory information for various reports or classes
of reports.
i.e.

tblControl
fldCtlIdent fldCtlValue

ExportDir C:\MyAppName\Reports\
ImportDir C.\MyAppName\Imports\
JohnsonReport C:\SpecialReports\Johnson\
NetworkReport
EffectiveYear 2006

That way a dLookup can get the proper directory AND if the location of
the app changes all I have to do is change the table and everything
works without having to go back in and find every place in every
module/macro/form that may have had a reference to a drive mapping

 

[Ron2006]

I just re-read your original post and realized that you COULD tell the
difference between Jan 11 and Nov 1. However the visual presentation of
all of the files would seem "cluttered" with the size of the name
changing so I still think that at least formating the month and year
would be more pleasing and easier to search when looking for a file.

But in the end it is your call.

Good luck.

 

[Douglas J. Steele]

Your point is a valid one, plus using yyyy-mm-dd has the advantage that it
makes the files easy to sort chronologically.

 

[Ron2006]

And when sorted by name they would come out like

January
October
November
December
February
March
etc