Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Posts
    4

    Red face Unanswered: Passing " to a SQL statement Urgent (but if you can't be bothered I understand)

    Hiya All,

    This is a bit of code that SHOULD pass some plain text to a Rowsource and thus filter the table for a specific work phrase etc...

    I'm tired and just can't make it work all help greatly appretiated as always

    Example of the SQL statement wanted
    SELECT tbl_product.ID, Trim([manufacturer] & Chr(32) & [Description] & Chr(32) & [Version]) AS [Desc]
    FROM tbl_product
    WHERE (((Trim([manufacturer] & Chr(32) & [Description] & Chr(32) & [Version])) Like "*unreal*"));

    CODE:
    Private Sub Search_text_BeforeUpdate(Cancel As Integer)
    sqlstr = "SELECT [ID], Trim([manufacturer] & chr(32) & [Description] & chr(32) & [Version]) AS [Desc] "
    sqlstr = sqlstr & "FROM tbl_product "
    If Search_text <> "" Then
    sqlstr = sqlstr & "WHERE (((Trim([manufacturer] & Chr(32) & [Description] & Chr(32) & [Version])) Like chr(34)*" & Search_text.Value & "*chr(34)));"
    Else
    sqlstr = sqlstr & ";"
    End If
    Search = 999
    Product_View.RowSource = sqlstr
    End Sub

    just can't seem to pass the text box value to the SQL statement

  2. #2
    Join Date
    Jul 2002
    Location
    Romania
    Posts
    122
    Try it with the following corrections:

    Private Sub Search_text_BeforeUpdate(Cancel As Integer)

    sqlstr = "SELECT [ID], Trim([manufacturer] & ' ' & [Description] & ' ' & [Version]) AS [Desc] "
    sqlstr = sqlstr & "FROM tbl_product "
    If Nz(Search_text ,"")>0 Then
    sqlstr = sqlstr & "WHERE (((Trim([manufacturer] & ' ' & [Description] & ' ' & [Version])) Like " & chr(34) &" *" & Search_text.Value & "*" & chr(34) & "));"
    Else
    sqlstr = sqlstr & ";"
    End If
    Search = 999
    Product_View.RowSource = sqlstr
    End Sub

    HTH,

    Dan

  3. #3
    Join Date
    Aug 2002
    Posts
    4
    Thanks,

    Just couldn't concentrate last night, worked very well

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •