locked
Access Query Problem with ' RRS feed

  • Question

  • Hi,

    I have a Query Where I check that item is already exists or not

    Select Item_name From Item_master Where Item_name = ' " & Itname &" ' "

    In then above query Itname have ' (Single quot) Which is supplied by customer.

    Example : Itname = 10' X 20' this is generate error.

    how i execute this query without error.


    Art Of Living Is Art Of Giving

    Monday, September 2, 2013 11:06 AM

Answers

  • Select Item_name From Item_master Where Item_name = ' " & Itname &" ' "

    In then above query Itname have ' (Single quot) Which is supplied by customer.

    Example : Itname = 10' X 20' this is generate error.

    Hi Pratush,

    If the string delimiter (for instance a single quote) is the same as a character within Itname, you can double that character in Itname for its literal value, using

        ... WHERE Item_name = '" & Replace(Itname,"'","''") & "'"

    Imb.

    Monday, September 2, 2013 1:00 PM

All replies

  • e.g. by sourrounding the value by double quotes:

    "Select Item_name From Item_master Where Item_name = " & Chr$(34) & Itname & Chr$(34)

    cu
    Karl
    *********
    Access-FAQ (German/Italian): http://www.donkarl.com

    Monday, September 2, 2013 11:23 AM
  • Select Item_name From Item_master Where Item_name = ' " & Itname &" ' "

    In then above query Itname have ' (Single quot) Which is supplied by customer.

    Example : Itname = 10' X 20' this is generate error.

    Hi Pratush,

    If the string delimiter (for instance a single quote) is the same as a character within Itname, you can double that character in Itname for its literal value, using

        ... WHERE Item_name = '" & Replace(Itname,"'","''") & "'"

    Imb.

    Monday, September 2, 2013 1:00 PM
  • I notice also that you are surrounding the value of the variable with spaces, which, assuming the values in the Item_name column do not themselves have a leading and trailing space character, would not find a match.

    A literal quotes character can be represented as a contiguous pair of quotes characters, so taking account of both of these points:

    "SELECT Item_name FROM Item_master WHERE Item_name = """ & Itname & """"

    Ken Sheridan, Stafford, England


    Monday, September 2, 2013 1:16 PM