Solved

Concatenation w/Text & variable - Variable data contains Quotes

Posted on 2013-05-10
10
401 Views
Last Modified: 2013-05-10
Here is the results>

UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = 3011 ' The Microsoft Office Access database engine could not find the object 'R_MeasNoAddDlet'. Make sure the object exists and that you spell its name and the path name correctly.'
WHERE tblAPList.ApNo= '503'

Current string:

        strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & "'" & Err.Description & "'" & _
                        " WHERE tblAPList.ApNo= '" & gApNo & "'"

What is the correct syntax?
0
Comment
Question by:Karen Schaefer
  • 5
  • 4
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39156524
are you going to use this just for information purposes ?  if not this will fail.


        strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & " " & Err.Description & " " & _
                        " WHERE tblAPList.ApNo= " & gApNo & ""
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 39156538
Try this ...

MsgBox strSQL4

Then post a screen shot showing the exact content of strSQL4.

This will eliminate having to hit and miss to come up with the correct syntax.

ET
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39156547
or this one


        strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & " " & Replace(Err.Description,chr(39),"")  & " " & _
                        " WHERE tblAPList.ApNo= " & gApNo & ""
0
 

Author Comment

by:Karen Schaefer
ID: 39156613
I am trying to update tbl log and then I want the code to continue to the next item on the looping list.

k
0
 

Author Comment

by:Karen Schaefer
ID: 39156621
Rey,

Tried your suggestions got the following error msg.

err
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Karen Schaefer
ID: 39156633
UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = 3011 ' The Microsoft Office Access database engine could not find the object 'R_MeasNoAddDlet'. Make sure the object exists and that you spell its name and the path name correctly.'
WHERE tblAPList.ApNo= '503'

This is the results of the sql string
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39156648
so you  are going to use the strSql...

if err.number=3011 then

dim strMsg as string

strMsg=err.number & " " & err.Description

    or this

strMsg=err.number & " " & replace(err.Description, chr(39),"")


_____

 strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = '" & _
                   strMsg & "'  WHERE tblAPList.ApNo= '" & gApNo & "'"


or


strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                   & chr(34) & strMsg & chr(34) & "  WHERE tblAPList.ApNo= '" & gApNo & "'"


.
0
 

Author Comment

by:Karen Schaefer
ID: 39156666
I want this not just if Err.number = a certain number I just want to input into the log the error message - so that the admin user can take action at a later time.

so Do I need to include the if err.number = ?????

k
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39156680
<so Do I need to include the if err.number = ?????>  NO need to.
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39156690
strMsg=err.number & " " & replace(err.Description, chr(39),"")

solved the issue with the inner quotes.  Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What .NET URL re-routing tool did I use? 2 37
Sub Reports 8 22
grouping logic 6 49
BMP **object links** severely inflated size of 2013 Access database 4 0
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now