Re: Single Quote in SQL Statement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Van T. Dinh (VanThien.Dinh_at_discussions.microsoft.com)
Date: 05/22/04 

Date: Sat, 22 May 2004 10:28:26 +1000

Try:

DoCmd.RunSQL ("Insert Into tbl_po_details ([POID],[ItemID],
[DESCRIPTION],[Quantity],[Unit_Price],
[ITEM_TYPE_ID_FOR_SELECTION],[GROUP_ON_PO],[PRODUCT])
values (" & PON & "," & ID & "," & Chr$(34) & BD & Chr$(34) & ",1," &
UP & "," & itid & ",-1,'" & p & "')")

BTW, it is easier to debug in you can construct the SQL String separately
and print the result of the concatenation so that you can see exactly what
is passed to JET database engine for processing. Something like:

Dim strSQL As String

strSQL = {as above}

Debug.Print strSQL

DoCmd.RunSQL strSQL

...

You may also like to check out the Execute Method which can also be used to
execute an Action SQL String which doesn't require confirmation. 

-- 
HTH
Van T. Dinh
MVP (Access)
"Dan" <dbarasch@pioneercomputertech.com> wrote in message
news:1052501c43f5d$aafb7090$a001280a@phx.gbl...
>
> I need to run a insert sql statement into one of my
> tables. As you can see, the sub get arguments that need to
> be put into the sql statement.  The problem that I am
> getting is that if the BD argument has a single quote in
> its value (Ex: 10' feet CAT5 Cable), the sql statement
> returns a error.  How can you make the statement handle
> strings that have a single quote.
>
> Dan
> =======================================================
> Public Sub BUNDLE(PON, ID, UP, itid, QTY, BD, p)
> Dim i As Integer 'counter
> DoCmd.SetWarnings (warningsoff)
> i = 0
> Do Until i = QTY
>
> DoCmd.RunSQL ("Insert Into tbl_po_details ([POID],[ItemID],
> [DESCRIPTION],[Quantity],[Unit_Price],
> [ITEM_TYPE_ID_FOR_SELECTION],[GROUP_ON_PO],[PRODUCT])
> values (" & PON & "," & ID & ",'" & BD & "',1," & UP & ","
> & itid & ",-1,'" & p & "')")
>
> End Sub

Relevant Pages

  • Re: Updating a table
    ... First, you are building a SQL string, but not using it. ... CurrentDb.Execute strSQL, dbFailOnError ... letters as sent. ... > Private Sub PrintLetter_Click ...
    (microsoft.public.access.formscoding)
  • Re: dynamic query help
    ... Write a query that does what you want. ... rstOpen function. ... SQL string in a variable, like strSQL I showed you, and check that it is ...
    (microsoft.public.access.queries)
  • Re: Error 3141 Cannot resolve
    ... you'll note that you have a comma in the SQL string after the field name. ... But the next line is the FROM clause, and there should be no comma between the last selected field name and the FROM keyword. ... strSQL = "SELECT tblMaterialMaster.Discount," ... Although this works with queries opened via the Access user interface -- Access will prompt the user for the parameter value -- it doesn't work with queries executed via DAO, because DAO has no means to prompt for parameters. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Search Form Using Three Criteria
    ... <MS ACCESS MVP> ... Dim strSQL As String ... ' build sql string for form's RecordSource ...
    (microsoft.public.access.forms)
  • Re: Search Form Using Three Criteria
    ... "Ken Snell MVP" wrote: ... Dim strSQL As String ... ' build sql string for form's RecordSource ...
    (microsoft.public.access.forms)