Output To fails when name contains forward slash 1

[TrekBiker]

I have a form containing a subform sfmDebtorsList with information about customers who have elected to receive Statements by email. A command button on the main form initiates creating the email and saving the attachment. The process works fine when customers have company names like Smith Ltd, but gives an Output to error if the name is Smith Ltd T/A Jones Ltd. It's the forward slash / that's causing it.

The relevant section of code is this, with the line DoCmd.OutputTo acOutputReport causing the problem. The names need to stay as they are so how is it best to get round the trip-up?

   'Create folder for statements unless it's already there
    path = "\\MOAKBBTERM16\Database\Statements\"
    If Len(Dir(path, vbDirectory)) = 0 Then MkDir path
    
   'Define recipient
    strBody = "To : " & Me.sfmDebtorsList.Form.Payee & "<BR><BR> Please find attached your latest statement. <BR><BR> Our terms : Payment by 15th day of the month" 
   
   'Define where to save a copy
    FileName = Me.sfmDebtorsList.Form.SearchName  
    FilePath = "\\MOAKBBTERM16\Database\" & FileName & ".pdf"
    path = path & FileName & ".pdf"
    
   'Save this statement
    DoCmd.OutputTo acOutputReport, "rptDebtorSingleStatement", acFormatPDF, path

   'Create temporary file
    DoCmd.OutputTo acOutputReport, "rptDebtorSingleStatement", acFormatPDF, FilePath

 

[Andrzejek]

Looks to me you run into illegal character for directory and/or file name :-(

>The names need to stay as they are
Well, sometimes they cannot

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks for confirming this Andy. My client will continue to use T/A and c/o for customer names and I can't control this. So I'll pursue the link you sent and see about removing the / before using the name in the Output To line. I don't think there are any other illegal characters but probably best to include all possibilities in the fix.

 

[TrekBiker]

Tried modifying the company name in the query feeding the email procedure. This was a dummy run to check the change, and it works fine.

SELECT Table1.Company, Replace([Company],"/","-") AS Stripped
FROM Table1;

To cater for every possibility how is it possible to extend the Replace statement to look for any of the illegal characters ~#%&*:<>?{|}/\[] ?

 

[Andrzejek]

From the link above, you can set up a simple Function [tt]ReplaceIllegalCharacters[/tt] and modify your code to:

SELECT Table1.Company, [blue]ReplaceIllegalCharacters([/blue]Company[blue],"-")[/blue] AS Stripped
FROM Table1;

BTW, I would use a shorter name for this Function myself

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Brilliant, thanks, I'll give this a go. And yes to a shorter name!

 

[Andrzejek]

Consider something else....
If the names of the Companies are typed, chances are this Function will do just fine.
But if you get those names from some other source, you may end up with some un-printable characters (Unicode, Non-ACSCI letters, etc.) Or, if people copy'n'paste Company names, you may end up with Tab, bullets, etc.
[ul]
[li]Animal Medicine and Taxidermy[/li]
[/ul]

Consider another approach - allow certain characters only (A-Z, a-z, 0-9, Space), and Replace anything else.

Function MakeItGood(ByRef strIn As String, ByRef strDel As String) As String
Dim strOut As String
Dim i As Integer

strOut = strIn

For i = 1 To Len(strOut)
    Select Case Asc(Mid$(strOut, i, 1))
        Case 65 To 90, 97 To 122, 48 To 57, 32[green]
            ' A to Z,   a to z,    0 to 9   Space[/green]
        Case Else
            strOut = Replace(strOut, Mid$(strOut, i, 1), strDel)
    End Select
Next

MakeItGood = strOut

End Function

Just a suggestion...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Love it Andy, works a treat! They'll always type in a company name so they can include what they like, with your suggestion making sure the emailing works.