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

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 otherstrange characterRun-time error '3075':Syntax error (missing operator) in query expression "C:\DVD APPZX\Appz Hp\hp drivers\hp\Drivers-Audio\Realteck AC'97 AudioDriver Update(English)' WHERE Tempinfo_Tbl.ID = 10165 ANDTempInfo_Tbl.DirFolder Like 'DIR*"The code replace in one step all the records from my TblTempInfo_Tbl:Actual Record: DIR Dups V.1.0\bas\2006\<Drive>:\Path\Folder\Folder....After Replace:  C:\Dups V.1.0\bas\2006If  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 tochange?This code is one of the most important in the whole proyectAny suggestion, ideas or help is more than welcomeThank you very much in advanceRegards,Antonio Macias'Initialize New EventDim sQL18 As StringDim MyTempInfoCount As IntegerDim SearchChar As StringDim GetFolder As StringDim MidPathGetFolder As StringDim MyTempInfoID As IntegerDim MyDirFolder As StringDim MyReplaceDirFolder As StringDim MyFilesFound As IntegerDim U As IntegerDim DirFolderA As StringDim DirFolderB As String'Initialize VariablesSearchChar = "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 = CurrentDbSet smars = smadb.OpenRecordset("TempInfo_Tbl", dbOpenDynaset)For U = 1 To MyTempInfoCountMyTempInfoID = 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 SETTempData_Tbl.FilesinDir = " & MyFilesFound & " , " & _"TempData_Tbl.DirPathOnly = '" & MyReplaceDirFolder & "' " & _"WHERE TempInfo_Tbl.ID= " & MyTempInfoID & " ANDTempInfo_Tbl.DirFolder Like 'DIR*' "CurrentDb.Execute sQL18, dbFailOnErrorsmars.MoveNextNext Usmars.Closesmadb.CloseSet smadb = Nothing
0
rebelscum0000
3/27/2007 3:39:28 PM
access 16762 articles. 2 followers. Follow

2 Replies
1336 Views

Similar Articles

[PageSpeed] 48

Dear Doug Steele,With the first option I get this errorCopile error:Expected: end of statement"TempData_Tbl.DirPathOnly =  " " " & MyReplaceDirFolder & " " " " & _And mark the second and third quotes of the lineWith 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 APPZX\Appz Hp\hp drivers\hp\Drivers-Audio\Realteck AC'97 AudioDriver Update(English)' WHERE Tempinfo_Tbl.ID = 10165 ANDTempInfo_Tbl.DirFolder Like 'DIR*"When is trying to replace this DIRC:\DVD APPZ X\Appz Hp\hp drivers\hp\Drivers\Driver-Audio\Realtek AC'97Audio Driver Update(English)sQL18 = _"UPDATE TempData_Tbl " & _"INNER JOIN TempInfo_Tbl " & _"ON TempData_Tbl.DirHashFiles = TempInfo_Tbl.DirFolder SETTempData_Tbl.FilesinDir = " & MyFilesFound & " , " & _"TempData_Tbl.DirPathOnly =  ' " & Replace(MyReplaceDirFolder, " ' "," ' '") & " ' " & _"WHERE TempInfo_Tbl.ID= " & MyTempInfoID & " ANDTempInfo_Tbl.DirFolder Like 'DIR*' "Also left a blank space in the Tbl TempData_Tbl, Field NameDirPathOnly[Space]C:\DVD APPZ XInmediate WindowUPDATE TempData_Tbl INNER JOIN TempInfo_Tbl ONTempData_Tbl.DirHashFiles = TempInfo_Tbl.DirFolder SETTempData_Tbl.FilesinDir = 3 , TempData_Tbl.DirPathOnly =  ' C:\DVDAPPZ X\Appz Hp\hp drivers\hp\Drivers\Driver-Audio\Realtek AC'97 AudioDriver Update(English) ' WHERE TempInfo_Tbl.ID= 10165 ANDTempInfo_Tbl.DirFolder Like 'DIR*'What could be the problem?Thanks in advanceRegards,Antonio Macias
0
rebelscum0000
3/27/2007 5:47:55 PM
On Mar 27, 12:04 pm, "Douglas J. Steele"<NOSPAM_djsteele@NOSPAM_canada.com> wrote:> 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 nowThanks againRegards,Antonio Macias
0
rebelscum0000
3/27/2007 7:14:41 PM
Reply:

Similar Artilces:

Re: Sharepoint and PST's
Good news! Microsoft has released a hotfix that adds a registry value = named AlwaysAllowSharePointPST to support links to SharePoint even with = DisablePST is set. See = http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;897658 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Sue Mosher [MVP-Outlook]" <suemvp@outlookcode.com> wrote in message = news:... Since DisablePST doesn't disable the opening of existing PST files, = w...

Re: cannot download updates for office mac 2004 student/teacher edition
Actually: Ping "download.microsoft.com". I think it will resolve to a different server than the one I am seeing in Australia. I deduce this from the fact that I am seeing an 8 millisecond ping time here, and that's not enough time for the packets to get from one side of the Pacific to the other, so I suspect they have some very interesting re-directing technology happening in the middle... Cheers -- Don't wait for your answer, click here: http://www.word.mvps.org/ Please reply in the group. Please do NOT email me unless I ask you to. John McGhie, Consultant Tec...

Re: IE URL Inquiry
Can you tell me where to find some of the examples? Thanks. ...

Strip apostrophe from cells
Hi. I am exporting data to Excel from an Access database. There is a large volume of data, and much of the text is being preceded by the apostrophe ('). This does not appear in the cell - only in the formula bar. Is there any function I can use to get rid of it? I've tried Clean, Left, Right, etc, but they don't seem to see it. Going into each cell is obviously not an option, and I'm stuck - please help!! Thanks a lot, Jennie. --- Message posted from http://www.ExcelForum.com/ this is because, preceding a number value with an apostrophe lets excel know it is text. if y...

excel VBA
How would i search a range of a whole column instead of specific cells like .Range("a1:a300")? --- Message posted from http://www.ExcelForum.com/ ..range("a:a") ..columns(1) ..range("a1").entirecolumn are a few ways. "Zygoid <" wrote: > > How would i search a range of a whole column instead of specific cells > like .Range("a1:a300")? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ahhh! Thanks!! I was trying .Column(1) i see i neede an "s" in there Thanks a...

need help parsing text to get xml
I have a string that contains the \", \t, \r, \n. I need to get the xml. sample below: "<?xml version=\"1.0\"?>\r\n<USERS xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns=\"http://www.slcorp.com\\xml\\slcorp_dtd_schema.xml\">\r\n\t<ACCT>GameTek</ACCT>\r\n\t<USER>\r\n\t\t<USER_ID>Mike</USER_ID></USER>\r\n\t</USERS>\r\n" I have tried replacing as follows so I can get the xml. I have tried 2 approaches (1) str = str.Replac...

Vlookup data wrong if the small value found are same
Dear Friends, I need to find the smallest 8 amounts from hundreds of data and look up the data of the corresponding amount. So I used the small function with referrence to the Rank column and found two -100,000.00 and each of them related to Joey and Keith. However, the vlookup function retrieved both data as Joey and lost Keith's Number and Name. Same problem occurs if retrieving the top 10 amounts with large function. Thanks for your great help in advance. Fanny Rank Amount Number Name 1 -156,250.00 8 John 2 -120,547.00 13 Peter 3 -103,100.00 541 Mary 4 -1...

Off topic
I don't suppose anyone can explain why I can read the messages in this newsgroup only via msnews.microsoft.com when I used to be able to get them via blueyonder.news ? -- Return email address is not as DEEP as it appears Sounds like a question for blueyonder. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Jack Schitt wrote: > I don't suppose anyone can explain why I can read the messages in this > newsgroup only via > msnews.microsoft.com > when I used to be able to get them vi...

Help with graph from exported information
I am trying to graph information from a spreadsheet that has been exported from another program (a time-recording program). It basically puts the information like this: matter 1 - 5 matter 2 - 10 matter 3 - 2 matter 2 - 5 matter 2 - 10 etc etc I want to create a graph that charts the TOTAL of all matter one, etc. I can't figure out how :( Am using Excel 2007. Thank you Susan Try this approach: http://peltiertech.com/Excel/ChartsHowTo/ConsolidateData.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://Peltier...

im new and need help
hi all hope everyones good i need some help with a project of mine and hoping this forum would be able to find it here for a gas bill calculator my spread sheet needs to do this. i want to place the this information as input. 2 dates a from and to date 2 readings( from meter) a specific number relating to the relevant area and charges a slot for if the meter is metric or imperial process i want the spreadsheet to calulate Kwh from the readings which i know how to do already. this is the part i need help with once the Kwh has been calculated i need to calculate depending on what months ...

Please help with access expression
Hello, I was hoping someone could assist with the following expression in access that combines the Left function and Len function: I have column called ID, which has about 700,000 records and each ID can be anywhere from 7 to 10 characters; however, I all IDs to have only 7 characters and this means removing characters that are greater than 7. For example, smith09 is fine, but smith09990 would need to reduced to smith09. Basically, i need an expression that will check every ID and, if ID > 7 characters, use the left function to cut it to 7 characters. Please let me know if yo...

Help! Restore/ Disaster Recovery exch 2000
I have an Exchange 2000 that the stores won't mount, I tried everything, I was able to mount them, but 12 hours later information store service stopped, then I was able to bring it back and now the databases won't mount, what I need help with now is that I want to bring the Exchange 2000 data to an Exchange 2003 server, how do I do this in recovery mode, any way I can bring the data to the new exchange server 2003? Check if database is in clean shutdown mode using eseutil /mh command. If it's dirty shutdown; you will need to restore database from backup. If backup is not av...

Removing macro #2
I am using Excel 97 (from Office Pro 97) on a new Vista machine. Every time I open a particular spreadsheet I get the option of enabling or disabling macros. I have no idea what this/these macro is all about - I never wrote a macro in my life -- and would like to know how to find it and delete it. Any help would be appreciated. --- Tony Vella, Ottawa, Canada http://indian-biographies.shorturl.com Debra Dalgleish shows how to clean up all the remnants of macros: http://www.contextures.com/xlfaqMac.html#NoMacros But if someone else developed the workbook, you may not want to delete any...

VBA forms
Hello, I'm using the following in a macro but it doesn't work: Range("A2:A65536").Select If Cells.Select = "0" Then Selection.ClearContents What should I change??? Hi what are you trying to achieve ? -- Regards Frank Kabel Frankfurt, Germany "One-Leg" <OneLeg@discussions.microsoft.com> schrieb im Newsbeitrag news:09BDF4EF-22F3-4223-B2FD-B45D8EB37D7F@microsoft.com... > Hello, > > I'm using the following in a macro but it doesn't work: > > Range("A2:A65536").Select > If Cells.Select = "0&quo...

re help
hi i have just installed vista on my comp and all is working well im using windows mail and just wanted some more stationery so i went to down load Microsoft's own but can i find it when it installs it it does not put it into the windows mail stationery file ? why not any help ...

Help with shariing excel file !
Hi, I have strange problem since a few weeks with an excel file I share. This file is stored on aW2003 SBS. It can only be modified by one person (it has a "modify" password is set). The file is *always* opened from the same station, by the same user ! I set this thru : "Save as", then "Tools", "General option". Now, sometimes, when this famous user opens the file, it gets an error, which tells him that... the file is already opened by ... him ! Of course, it can't modify it since the file is exclusive ! I tried to reboot the station, nothing ! The ...

RE: Outlook 2003 won't retain POP3 password
I had someone with a similar problem that I have been trying to help out. Try this link: http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b290684 This might help the problem. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com ...

Allow completed service activities to be re-opened
Depending on the role/rights of the user, being able to open completed service activities would be useful - activities may have been accidently closed, or if representing a service activity over a few days (or weeks) there may be some difference of opinion whether a task is completed necessitating the item to be re-opened. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open th...

Trying to filter properly with SQL
I have a view i am trying to setup that is not working: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AND (("AssignmentID" IS NULL OR "AssignmentID" < 0) AND "urn:schemas-microsoft-com:office:office#Keywords" <> 'Daily Personal') This works to show all non-project related items: ("DAV:isfolder" = false AND "DAV:ishidden" = false) AND (("AssignmentID" IS NULL OR "AssignmentID" < 0) ) and this works to show all items without the category "Daily Personal": "urn:s...

SQL Query needed
Sample Data Type # Amt # Trno # DC # Code # Subcode ADV # 1000 # 1 #D # 103625 # ADV # 1000 # 1 #C # 103400 # S1 ADV # 2000 # 2 #D # 103625 # ADV # 2000 # 2 #C # 103400 # S2 VES # 5000 # 1 #D # 103400 # S1 VES # 5000 # 1 #C # 300070 # S1 Now I wanna find out the details of Code & Subcode of Type(VES) which does not match with Type ADV Expected result should come with subcode of the other record ADV # 2000 # 2 #D # 103625 # S2 Thanks in advance Nirene -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/2010...

File Not Found msg when run Excel
What would cause a "file not found" error upon starting Excel (w/o any reference to a file to be loaded on startup). I've checked in the VBA screen and can find no macros set to fire on startup. You can load workbooks each time when excel starts via the Tools|Addins dialog. If you've moved/deleted one of those files, excel will yell at you that it can't find it. If you start excel (ignore the message) and then tools|addins you can uncheck the selected addins. When you uncheck the missing addin, excel will notice that it can't find it and ask if you want to remo...

ot
OT - OFF - TOPIC Hola amig@s; El gobierno Colombiano en su continuo inter�s de ofrecer oportunidades acad�micas y profesionales a todos sus habitantes y a todos los interesados en capacitaci�n ha preparado una de las mejores plataformas de educaci�n virtual, con cursos virtuales "certificados" con cero costo. Los invito a visitar la WEB del Servicio nacional de Aprendizaje, nacionales o extranjeros pueden acceder a una de las plataformas educativas gratuitas mejor estructuradas y pagadas por todos los Colombianos. Hay cursos de inform�tica, office, outlook , cursos de Excel...

RE: server based archive solution
i was looking for a good server based email archive solutions...we currently have inidividual users using Outlook archive but i wanted to move the "responsibility" of archiving to the server-level ; the MSFT exchange sites lists some vendors (http://www.microsoft.com/exchange/partners/archivingandcompliance.asp) but i was wondering what other people had used and what they liked/disliked about the product they chose (or evaluated) ; thanks for any help!! Honestly, it all depends upon your environment and what your business requirements are. One client had to find a financially/exc...

HELP!!!!
Working with a friends money 99 program. Go out to update internet information and i get 2- The Symbol US:JASSX is invalid and The symbol US:jmed is invalid....any ideas?? Also, Product information: money was unable to complete the operation. The internet banking, or brokerage server you were trying to contact encountered an error. please try your call again later. (SMC) My e-mail is Altec14942@aol.com if anyone can help on these to problems....THANKS 1. well.. all that suggests that you have a firewall or a router or your connection is not setup so money can read it.. it you have a...

Installing 2007 OHS after uninstalling word, ppt and excel 2003 pro
I have just uninstalled Word, PPT and Excel 2003 Pro so I can install these 3 programs in the 2007 versions. I am fearful that I might not be able to use my Outlook 2003 after I install these new versions. My questions are: 1. Will Outlook 2003 use the Word 2007 email editor now (I know there were issues in the past)? 2. When I install the new versions do I have to keep any of the information from the 2003 version? Will it prompt me as I install? 3. If there are major issues I can't resolve right away can I simply uninstall my new versions and reinstall my 2003 again so I ...