Solved

Concatenation w/Text & variable - Variable data contains Quotes

Posted on 2013-05-10
10
409 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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
 

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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 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