Solved

Concatenation w/Text & variable - Variable data contains Quotes

Posted on 2013-05-10
10
400 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

747 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

13 Experts available now in Live!

Get 1:1 Help Now