Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009

    Unanswered: VBA Replace funtion

    I'm currently working with an Access 2007 database that runs off of a .csv file that is output from another older terminal based database which we call HostAccess. In HostAccess we have part numbers with illegal charachters ( /\*<>?, ect.) and these part numbers are passed over to a table in our Access database. I use these part numbers to open pdf files with the same part number i.e. if i have a record with the part number 123-4567 the database opens the file 123-4567.pdf. Along with that i use this database to create excel files (123-4567.xlsx). The problem i'm having is some of the part numbers have been created with the illegal characters in them, 123-4567-8" or 123-456-3/8. Today i found that using the VBA Replace function i can deal with some of these instances but it doesn't work for the part numbers with the quotation mark in it. Any ideas how i can deal with replacing with illegal character on the fly as i'm doing with the slash character. The code below is what i'm using to replace the slash character with an underscore (which is allowed). Any help would be greatly appreciated.
    Private Sub Command53_Click()
        Dim PartNum
        Dim PartDesc
        PartNum = Replace([Part Number], "/", "_")
        PartDesc = Replace([Part Description], "/", "_")
               If Len(Dir("\\TPIS01\engineering\drawing pdfs\drawings\" & PartNum & ".pdf")) > 0 Then
                File = "\\TPIS01\engineering\drawing pdfs\drawings\" & PartNum & ".pdf"
                    File = "\\TPIS01\engineering\drawing pdfs\drawings\" & PartDesc & ".pdf"
                        End If
        ShellExec File
    End Sub

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    The ASCII for a Quotation Mark is 34, so you can use

    Replace([Part Number], Chr(34), "_")

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts