How to Add Date to OutputTo File Name

[kcmike]

Currently, I'm doing a simple OutputTo statement in VBA to export query results to an Excel file and I'm appending today's date to the output file name.

Private Sub Command1_Click()

Dim TodaysDate As String

TodaysDate = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "mm-dd-yy")

DoCmd.OutputTo acOutputQuery, "FQ DAILY 7366 REPORT FUND 3801", "ExcelWorkbook(*.xlsx)", "I:\fullservice\Common\Amy's Group\REITs\FS Investments\3801(R12749 and R07366)\R07366\FQ 7366 Daily 3801 (" & TodaysDate & ").xlsx", False, "I:\fullservice\Common\Amy's Group\REITs\FS Investments\Template Files\FQ 7366 Daily 3801.xlsx", , acExportQualityPrint
DoCmd.Beep
MsgBox "Spreadsheet Transferred Successfully", vbOKOnly, "Transfer Results"

End Sub

However, what I really need is the prior business date appended to the file name instead of today's date. I have a ODBC table available to me that contains this date, but I don't know how to extract that date to then use it as part of the exported file name.

Please help...

Thanks!
Mike

 

[mdlueck]

However, what I really need is the prior business date appended to the file name instead of today's date.

This by itself is "date math"... horribly nasty business. The least of which is worrying about all of the "second Tuesday's of the week".

I have a ODBC table available to me that contains this date, but I don't know how to extract that date to then use it as part of the exported file name.

Now, given that point, YES that is a simple path to the data you seek after.

What else can you tell us other than "ODBC table contains the date"? Is the application already connected to that table? What SELECT would be the correct one to retrieve only that piece of data? Once that is known, you can stick that SQL into an ADO type query, and end up with a VBA variable which contains the data you are seeking.

 

[kcmike]

This by itself is "date math"... horribly nasty business. The least of which is worrying about all of the "second Tuesday's of the week".

Exactly! That's why I'm so lucky that I already have a system-generated (mainframe) date based on a system holiday table and several other code variables...

Now, given that point, YES that is a simple path to the data you seek after.

What else can you tell us other than "ODBC table contains the date"? Is the application already connected to that table? What SELECT would be the correct one to retrieve only that piece of data? Once that is known, you can stick that SQL into an ADO type query, and end up with a VBA variable which contains the data you are seeking.

The table can be easily queried by either directly linking it to my current database or it can be hit via pass-through query. It's a very simple one-data-record table that has one column that contains the date I want to use. It is updated each night by our mainframe with the new previous business date. The connection to the external table is made through the local machine's IBM DB2 configuration, hence ODBC.

The SQL to extract the date would look like this...

SELECT
I12.CURRENT_SUPERSHEET

FROM
DBO.SUPERSHEET I12

and it returns the previous business date, in this case "8/20/2012"

I must admit, I'm still learning VBA and in the grand scheme of things, probably only have a fairly shallow understanding of this stuff, but I'm learning... so please be gentle...

 

[mdlueck]

Yes, pass-through query to the host DB2 database would be preferable.

Public Function LocateDate() As String
On Error GoTo Err_LocateDate

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset
  Dim strSQL As String

  strSQL = "SELECT I12.CURRENT_SUPERSHEET" & vbCrLf & _
           "FROM DBO.SUPERSHEET AS I12"

  'Define attachment to database table specifics
  Set adoCMD = New ADODB.Command
  With adoCMD
    [COLOR=Red][B].ActiveConnection = CurrentProject.Connection[/B][/COLOR]
    .CommandType = adCmdText
    .CommandText = strSQL
    Set adoRS = .Execute()
  End With

  With adoRS
    'Was no record found?
    If .BOF Or .EOF Then
      LocateDate = vbNullString
    Else
      'Fetch the value found
      LocateDate = Nz(adoRS!CURRENT_SUPERSHEET, vbNullString)
    End If

    'Close the database table
    .Close
  End With

Exit_LocateDate:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

Err_LocateDate:
  Call errorhandler_MsgBox("Class: clsObjDemoTbl, Function: LocateDate()")
  LocateDate = vbNullString
  Resume Exit_LocateDate

End Function

Now, you will need to update the RED LOC to provide the correct ODBC connection string to get to your server. The LOC I provided connects to the local Access database.

Once you have the date retrieve, you may adjust the date format... perhaps you will want to reformat it to YYYYMMDD format.

 

[kcmike]

Excellent! Thank you!