Topic: Oh! NOOO! APOSTROPHE FOUND RE: Replace and Removing SQL VBA HELP!!!

[rebelscum0000]

Dear All,

All nigth long and still do not know how to fix this query,
This works fine until finds an apostrophe and I guess any other
strange character

Run-time error '3075':

Syntax error (missing operator) in query expression "C:\DVD APPZ
X\Appz Hp\hp drivers\hp\Drivers-Audio\Realteck AC'97 Audio
Driver Update(English)' WHERE Tempinfo_Tbl.ID = 10165 AND
TempInfo_Tbl.DirFolder Like 'DIR*"

The code replace in one step all the records from my Tbl
TempInfo_Tbl:

Actual Record: DIR Dups V.1.0\bas\2006\
<Drive>:\Path\Folder\Folder....
After Replace: C:\Dups V.1.0\bas\2006

If I add doble quotes to avoid this problem in the line
"TempData_Tbl.DirPathOnly = '"" & MyReplaceDirFolder & ""' " & _
I get in my Tbl record only ...
" & MyReplaceDirFolder & "

What I am doing wrong, what can I do to fix it or what do I have to
change?
This code is one of the most important in the whole proyect
Any suggestion, ideas or help is more than welcome

Thank you very much in advance

Regards,
Antonio Macias


'Initialize New Event
Dim sQL18 As String
Dim MyTempInfoCount As Integer
Dim SearchChar As String
Dim GetFolder As String
Dim MidPathGetFolder As String
Dim MyTempInfoID As Integer
Dim MyDirFolder As String
Dim MyReplaceDirFolder As String
Dim MyFilesFound As Integer
Dim U As Integer
Dim DirFolderA As String
Dim DirFolderB As String

'Initialize Variables
SearchChar = "DIR"
'The name for the chosen file or directory
'<Drive>:\Folder
GetFolder = MyDlg.ReturnFilePath
LenGetFolder = Len(GetFolder)
'Folder
MidPathGetFolder = Mid(GetFolder, 4, LenGetFolder)

MyTempInfoCount = DCount("*", "TempInfo_Tbl")
DirFolderA = SearchChar & " " & MidPathGetFolder & "\"
DirFolderB = GetFolder & "\"
'Debug.Print Folder A RESULT DIR Dups V.1.0\
'Debug.Print Folder B RESULT C:\Dups V.1.0\

Set smadb = CurrentDb
Set smars = smadb.OpenRecordset("TempInfo_Tbl", dbOpenDynaset)

For U = 1 To MyTempInfoCount

MyTempInfoID = smars.Fields("ID")
MyDirFolder = smars.Fields("DirFolder")
MyReplaceDirFolder = Left(Replace(MyDirFolder, DirFolderA,
DirFolderB), Len(Replace(MyDirFolder, DirFolderA, DirFolderB)) - 1)
MyFilesFound = smars.Fields("FilesFound")

sQL18 = _
"UPDATE TempData_Tbl " & _
"INNER JOIN TempInfo_Tbl " & _
"ON TempData_Tbl.DirHashFiles = TempInfo_Tbl.DirFolder SET
TempData_Tbl.FilesinDir = " & MyFilesFound & " , " & _
"TempData_Tbl.DirPathOnly = '" & MyReplaceDirFolder & "' " & _
"WHERE TempInfo_Tbl.ID= " & MyTempInfoID & " AND
TempInfo_Tbl.DirFolder Like 'DIR*' "

CurrentDb.Execute sQL18, dbFailOnError

smars.MoveNext

Next U
smars.Close
smadb.Close
Set smadb = Nothing

 

[Douglas J. Steele]

Access can be a little tricky when you have single or double quotes in the
string.

Exagerated for clarity, right now you have:

"TempData_Tbl.DirPathOnly = ' " " & MyReplaceDirFolder & " " ' " & _

You need either

"TempData_Tbl.DirPathOnly = " " " & MyReplaceDirFolder & " " " " & _

or

"TempData_Tbl.DirPathOnly = ' " & Replace(MyReplaceDirFolder, " ' ", " ' '
") & " ' " & _


I talked about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/djsteele/SmartAccess.html

 

[rebelscum0000]

Dear Doug Steele,

With the first option I get this error

Copile error:

Expected: end of statement

"TempData_Tbl.DirPathOnly = " " " & MyReplaceDirFolder & " " " " & _

And mark the second and third quotes of the line

With the second option you gave me:

I still having the same error in the same record (ID= 10165)

Run-time error '3075':

Syntax error (missing operator) in query expression "C:\DVD APPZ
X\Appz Hp\hp drivers\hp\Drivers-Audio\Realteck AC'97 Audio
Driver Update(English)' WHERE Tempinfo_Tbl.ID = 10165 AND
TempInfo_Tbl.DirFolder Like 'DIR*"

When is trying to replace this DIR

C:\DVD APPZ X\Appz Hp\hp drivers\hp\Drivers\Driver-Audio\Realtek AC'97
Audio Driver Update(English)

sQL18 = _
"UPDATE TempData_Tbl " & _
"INNER JOIN TempInfo_Tbl " & _
"ON TempData_Tbl.DirHashFiles = TempInfo_Tbl.DirFolder SET
TempData_Tbl.FilesinDir = " & MyFilesFound & " , " & _
"TempData_Tbl.DirPathOnly = ' " & Replace(MyReplaceDirFolder, " ' ",
" ' '") & " ' " & _
"WHERE TempInfo_Tbl.ID= " & MyTempInfoID & " AND
TempInfo_Tbl.DirFolder Like 'DIR*' "

Also left a blank space in the Tbl TempData_Tbl, Field Name
DirPathOnly

[Space]C:\DVD APPZ X

Inmediate Window

UPDATE TempData_Tbl INNER JOIN TempInfo_Tbl ON
TempData_Tbl.DirHashFiles = TempInfo_Tbl.DirFolder SET
TempData_Tbl.FilesinDir = 3 , TempData_Tbl.DirPathOnly = ' C:\DVD
APPZ X\Appz Hp\hp drivers\hp\Drivers\Driver-Audio\Realtek AC'97 Audio
Driver Update(English) ' WHERE TempInfo_Tbl.ID= 10165 AND
TempInfo_Tbl.DirFolder Like 'DIR*'

What could be the problem?

Thanks in advance

Regards,
Antonio Macias

 

[Douglas J. Steele]

You seem to have left the spaces in: I said I was putting them in to make it
more obvious.

It's either

"TempData_Tbl.DirPathOnly = """ & MyReplaceDirFolder & """" & _

or

"TempData_Tbl.DirPathOnly = '" & Replace(MyReplaceDirFolder, "'", "''") &
"'" & _

 

[rebelscum0000]

You seem to have left the spaces in: I said I was putting them in to make it
more obvious.

Ops! Sorry I am little tired, but now I going home with a great smile,
thanks you!!

It works perfect now

Thanks again

Regards,
Antonio Macias