Solved

Concatenation w/Text & variable - Variable data contains Quotes

Posted on 2013-05-10
10
404 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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!

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.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

773 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