[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

Concatenation w/Text & variable - Variable data contains Quotes

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
Karen Schaefer
Asked:
Karen Schaefer
  • 5
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
Rey Obrero (Capricorn1)Commented:
or this one


        strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & " " & Replace(Err.Description,chr(39),"")  & " " & _
                        " WHERE tblAPList.ApNo= " & gApNo & ""
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Karen SchaeferAuthor Commented:
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
 
Karen SchaeferAuthor Commented:
Rey,

Tried your suggestions got the following error msg.

err
0
 
Karen SchaeferAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Karen SchaeferAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<so Do I need to include the if err.number = ?????>  NO need to.
0
 
Karen SchaeferAuthor Commented:
strMsg=err.number & " " & replace(err.Description, chr(39),"")

solved the issue with the inner quotes.  Thanks.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now