Problem with Snapshot Naming 1

[gaRed]

I have a form that generates production reports from an access db. The form gives the user the option to generate reports in Microsoft Snapshot format, via the click of a button. When the user clicks this button, a Snapshot file is created and stored in a folder on the user's c: drive. I coded the on_click event of the button to name the Snapshot file as follows: "reportName_startDate_endDate". The start and end dates represent the date range the user specified for the data.
The issue is, instead of adding the correct dates to the name of the file, the program adds a bogus number or date. The file name will come out as "reportName_53_51". After a few changes it will give a Feb. date, which is also wrong. Can anyone help?

 

[allanon]

Post the line for generating the name.

 

[gaRed]

Here's the code for the button that generates the snapshot.
Thanks!


Private Sub cmdSnapshot_Click()
Dim rsStartDate, rsEndDate As DAO.Recordset
Dim strSQL As String
Dim MyWorkDate As Date
Dim RptName As String
Dim dSQL As String
Dim RptPath As String
Dim RptPeriod As String
Dim RptStart As String
Dim RptStartPeriod As String

strSQL = "SELECT [tblProd].[Date] FROM tblProd WHERE [tblProd].[Record_ID] = " & [Forms]![frmDateRange]![cboFinishDate]
Set rsEndDate = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)
If rsEndDate.RecordCount > 0 Then
rsEndDate.MoveFirst
Else
Exit Sub
End If

If Not (IsNull([Forms]![frmDateRange]![cboStartDate])) Then
strSQL = "SELECT tblProd.[Date] FROM tblProd WHERE tblProd.[Record_Id] = " & [Forms]![frmDateRange]![cboStartDate]
Set rsStartDate = CurrentDb.OpenRecordset(strSQL, dbopensnapshot)
If rsStartDate.RecordCount > 0 Then
rsStartDate.MoveFirst
Else
Exit Sub
End If
End If


'If Day = Not Null Then
If Not (IsNull([Day])) Then
'strSQL = "SELECT * FROM tblProd WHERE tblProd.[Date] "
strSQL = "SELECT tblProd.*, tblCreel.* FROM tblProd INNER JOIN tblCreel ON tblProd.Record_ID = tblCreel.Record_ID "
strSQL = strSQL & " WHERE tblProd.[Date] BETWEEN #" & rsEndDate!Date & "# AND #"

Select Case Day
Case 1: MyWorkDate = DateAdd("d", -1, rsEndDate!Date)
RptStartPeriod = "1 Day" '**
Case 2: MyWorkDate = DateAdd("ww", -1, rsEndDate!Date)
RptStartPeriod = "1 Week" '**
Case 3: MyWorkDate = DateAdd("m", -1, rsEndDate!Date)
RptStartPeriod = "1 Month" '**
Case 4: MyWorkDate = DateAdd("m", -3, rsEndDate!Date)
RptStartPeriod = "1 Quarter" '**
Case 5: MyWorkDate = DateAdd("m", -6, rsEndDate!Date)
RptStartPeriod = "3 Quarters" '**
End Select
strSQL = strSQL & MyWorkDate & "#"
Else
strSQL = "SELECT tblProd.*, tblCreel.* FROM tblProd INNER JOIN tblCreel ON tblProd.Record_ID = tblCreel.Record_ID "
strSQL = strSQL & "WHERE tblProd.[Date] BETWEEN #"
strSQL = strSQL & rsEndDate!Date & "# AND #"
strSQL = strSQL & rsStartDate!Date & "#"
'RptStartPeriod = cboStartDate '**
'RptStartPeriod = DatePart("m", [cboStartDate]) & "_" & DatePart("d", [cboStartDate]) & "_" & Right(DatePart("yyyy", [cboStartDate]), 2)
RptStartPeriod = Forms![frmDateRange]![cboStartDate].Column(1)
End If
If Shift1 Or Shift2 Or Shift3 Or Shift4 = Not Null Then
strSQL = strSQL & "AND ((tblProd.Shift)= [Forms]![frmDateRange]![Shift1] Or (tblProd.Shift)= [Forms]![frmDateRange]![Shift2] Or (tblProd.Shift)= [Forms]![frmDateRange]![Shift3] Or (tblProd.Shift)= [Forms]![frmDateRange]![Shift4])"
End If
SetMyReportSQL (strSQL)


DoCmd.Hourglass True
Application.Echo False, "Please Wait...............Now generating SnapShot"
RptPath = "C:\Creel\Reports\"
'RptStart = DatePart("m", [cboFinishDate]) & "_" & DatePart("d", [cboFinishDate]) & "_" & Right(DatePart("yyyy", [cboFinishDate]), 2)
'RptPeriod = DLookup("[tblMonths]![MonthName]", "[tblMonths]", "[tblMonths]![ID] = DatePart('m',[cboFinishDate])&quot & DatePart("d", cboFinishDate)
'RptPeriod = cboFinishDate
RptPeriod = Me!cboFinishDate.Column(1)
RptName = "rptDetailByDay"


Application.Echo False, "Now outputting a detail report"
DoCmd.OutputTo acOutputReport, RptName, "Snapshot Format", RptPath & "Report_" & RptName & "_" & RptPeriod & "- " & RptStartPeriod & ".snp", False

DoCmd.Hourglass False
Application.Echo True
'DoCmd.SetWarnings True

Exit_cmdSnapshot_Click:
Exit Sub

Err_cmdSnapshot_Click:
'MsgBox Error$
Resume Exit_cmdSnapshot_Click

End Sub

 

[allanon]

Seem that you are selecting your dates from a combo box. I did the same thing. Put a bunch of dates into a combo box. Selected them. Put them together. Make sure you do a debug.print on Me!cboFinishDate.Column(1) and the other one to ensure you are getting the right info. Here is the line I used and it worked:

Debug.Print "ThisReport_" & Format(Me.Combo0.Column(1), "ddmmyyyy&quot & "_" & Format(Me.Combo2.Column(1), "ddmmyyyy&quot & ".snp"

And i get:

ThisReport_01012003_31122003.snp

 

[gaRed]

Is there a way to format the date so that it is easier to read? I have a feeling that my users will complain about 1-1-2003 and 21-31 2003 represented as 01012003_31122003.
Thanks for your help!

 

[gaRed]

Nevermind on the date format question. I've got it under control.
Thanks!