Thread: VBA Replace funtion
12-05-11, 17:22 #1Registered User
- 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" Else File = "\\TPIS01\engineering\drawing pdfs\drawings\" & PartDesc & ".pdf" End If ShellExec File End Sub
12-05-11, 17:59 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
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 foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007