Auto-Generate File Name with Field Content...

[PPHChris]

Hello all!

I have a DB with a query that I need to get the data out to excel while autogenerating the file name with a field.  Initially, I wanted a date range if possible... 

IE.  [COLOR=blue][b]NewGradList_01-01-08_to_11-01-08.xls[/b][/color]

Previously, I was using a date stamp -> 
[b]="NewGradList_" & Format(Date(),"mmddyy")[/b] ; however now I need to use a range.  These ranges will vary each time the report is ran by the 'end-user'.  When the Parameters are entered -> 

[i][Enter Start Day ~ Example mm/dd/yy][/i] and [i][Enter End Day ~ Example mm/dd/yy][/i]  I was going to append those to the table and pull those dates into the File name, then once the Query is finished I was using a SendObject-Range only function to include all fields except for the date paramenters I just appended to the query.

Not sure if that all made any sense =)  I know its a lot of loaded questions!  Any assistance or insight would be most appreciated!  I am willing to do it in SQL or VB... just not sure of the steps.

Thank you all!

 

[LisaWay]

You have described what you're trying to do, but I cannot tell what you're having trouble with. Please ask a specific question.

Regards,
Lisa

 

[PPHChris]

I am having trouble with auto generating the file name based on a field. Ultimately, I would like to write a script/macro to pull a field and use that field as the filename. Once the parameter's of the query are typed in by the end user, I will append that data to the table in this column (Its a date range).

*All records will have this same stagnant field name in the column

Does that help?

 

[LisaWay]

Have you already created the query? Does it ask for the dates? Does it create a table with the dates in one or more fields? If so, what is the name of the table, and what are the names(s) of the date field(s)?

Regards,
Lisa

 

[PPHChris]

Thank you for your rapid response's Lisa!

+ Yes I created the Query -> [COLOR=blue]3b_WAGE Payroll Summary 207642[/color]
+ Yes I set the criteria to as for specific dates within a range; ie ->  [b]'Between [i][Enter Start of Month ~ Example: 11/1/2008][/i] And [i] [Enter End of Month ~ Example: 11/30/2008]'[/i][/b]
+ No, I have not yet created the 'Append' portion of these 2 date ranges into seperate fields.  However the 1st would be [b]Start Date[/b] and the second would be [b]End Date[/b]

The table name is [COLOR=blue]mke_2a DED Payroll Summary 207642[/color]

In a perfect world... once I do the SendObject its going to take [COLOR=blue]mke_2a DED Payroll Summary 207642[/color] and attach it in an email with the file name as [COLOR=blue]Deduction Summary_[i]Start Date[/i]-[i]End Date[/i].xls[/color]

Thank you!  
Chris

 

[LisaWay]

Here's the date-related part of the query [3b_WAGE Payroll Summary 207642]:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT InputTable.ID, InputTable.MyDate, [Start Date] AS [Start Date], [End Date] AS [End Date] INTO TempTable
FROM InputTable
WHERE (((InputTable.MyDate)>=[Start Date] And (InputTable.MyDate)<=[End Date]));

Here's the code to create and send the table:

    Dim newName As String
    
    DoCmd.OpenQuery "3b_WAGE Payroll Summary 207642" ' Create [TempTable]
    newName = "Deduction Summary_" & Format(DFirst("[Start Date]", "TempTable"), "mmddyy") & "-" & Format(DFirst("[End Date]", "TempTable"), "mmddyy")
    DoCmd.Rename newName, acTable, "TempTable" ' Rename [TempTable]
    DoCmd.SendObject acSendTable, newName, acFormatXLS, "test@test.com" ' Send spreadsheet to test@test.com
    DoCmd.DeleteObject acTable, newName ' Delete [TempTable]

Regards,
Lisa