Filename Syntax in Macro

[misssaraliz]

I posted a few weeks ago on this same issue but didn't get any resolution so I'm going to try again. Thanks to all of you who tried to help.

I need to know the syntax for creating a file that will automatically be saved with the date the report is run. Everything is set up in a macro in Access 97 using the "Output To" action. I just can't get the syntax right in the "Output File" section of the macro.

Here is what I want the filename to be(using today's date of 2/27/02):
C:\My Documents\CompDB\Accountability\Compliance022702.xls

The macro is running off of an option group in a form so the user will pick which report they want to run. Then depending on which button they choose that particular part of the macro will run and the new filename will be created.

Thanks for the help.

 

[Guest_imported]

Here is a function that I wrote to do the same thing. You can call the code in a macro. I am only an amateur so the code may be a little messy. Hope it helps, it works for me.

Public Function acs_output()
Dim dayset As String
Dim monthset As String
Dim yearset As String
Dim hourset As String
Dim minuteset As String
Dim dayset1 As String
Dim CompleteDate As String
Dim NEWRECORDS As Integer

If day(Now()) = 1 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 2 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 3 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 4 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 5 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 6 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 7 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 8 Then
dayset = "0" & day(Now())
ElseIf day(Now()) = 9 Then
dayset = "0" & day(Now())
Else: dayset = day(Now())
End If

If month(Now()) = 1 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 2 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 3 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 4 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 5 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 6 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 7 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 8 Then
monthset = "0" & month(Now())
ElseIf month(Now()) = 9 Then
monthset = "0" & month(Now())
Else: monthset = month(Now())
End If

yearset = year(Now())

If hour(Now()) = 1 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 2 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 3 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 4 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 5 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 6 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 7 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 8 Then
hourset = "0" & hour(Now())
ElseIf hour(Now()) = 9 Then
hourset = "0" & hour(Now())
Else: hourset = hour(Now())
End If

If minute(Now()) = 1 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 2 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 3 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 4 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 5 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 6 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 7 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 8 Then
minuteset = "0" & minute(Now())
ElseIf minute(Now()) = 9 Then
minuteset = "0" & minute(Now())
Else: minuteset = minute(Now())
End If
CompleteDate = monthset + dayset + yearset + "_" + hourset + minuteset
NEWRECORDS = False
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("ACS_REQUEST Without Matching ACS_SERVICE_ORDERS&quot
If rst.RecordCount = 0 Then

Else: DoCmd.OutputTo acOutputTable, "acs_request", acFormatXLS, "h:\remote_sites\acs\request_archive\acs_request_" + CompleteDate + ".xls"

NEWRECORDS = False
End If
rst.Close
dbs.Close
'Debug.Print CompleteDate
End Function

 

[Guest_imported]

This runs only if the select query result is greater than 0.