Replace Function question

[metrodub]

In my report form, I have an area where a user can run a report to find out who took a course (selected from an unbound combobox) during a certain year (selected from another unbound combobox). When the user presses the run report button an OutputTo function is triggered. The resulting report (usually in Excel 97-2003 format) is saved in a folder on a share drive with the naming convention of [course title]_[year].xls where the course title is the chosen course and the year is the chosen year. I'm able to do that with no problem. Unfortunately, there are some courses that contain colons ) in their name. Since you cannot use colons in filenames, the output is saved as _[year].xls. I have tried using the replace function to replace any colons with dashes (-). The only problem now is that when the query runs in the outputto function, there won't be any results for courses with a colon because the query now has a dash in it and the data has a colon in it.

How can I fix the coding so that the query is run first and then the file is saved with the dash in place of the colon?

Here is my code, minus the replace function, for the click event of the run reports button:

Private Sub cmdCourseReport_Click()
On Error GoTo Err_cmdCourseReport_Click

Dim strFile, strOutFile As String
Dim strQuery As String
Dim myDir, strDoc As String

strFile = "my shared drive"
strQuery = "qryCourse_" & Me.cboCourseYear


myDir = Dir(strFile, vbDirectory)

If myDir = "" Then
    MkDir (strFile)
End If


strOutFile = strFile
strDoc = strOutFile & Me.cboCourseReport & "_" & Me.cboCourseYear & ".xls"

DoCmd.OutputTo acOutputQuery, strQuery, , strDoc, True


Exit_cmdCourseReport_Click:
    Exit Sub
    
Err_cmdCourseReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdCourseReport_Click

End Sub

 

[Eupher]

metrodub,

How about this:

strDoc = strOutFile & [COLOR=blue][b]Replace(Me.cboCourseReport, ":", "-")[/b][/color] & "_" & Me.cboCourseYear & ".xls"

HTH...

Ken S.