string in a query

[cmac2210]

All
I have a textbox that contains values seperated by a comma (exampel: tree, boat, house). I am trying to do an sql query that builds a table from another table only if the record has either of the items listed in the field. If I have only 1 item in the text box (boat), the table is built just fine, but if there are more than one, it give me no records at all. I believe this has something to do with the " " that the query puts around the criteria, and I have tried numerous things to try to add them my self (chr (34) or ' ), but I am at my wits end. Is there anyone that has any idea how to fix this?

 

[RuralGuy]

You will need to post the SQL for the query for us to make suggestions.

 

[cmac2210]

Thanks...

Private Sub Command28_Click()
If Not Me.Text26.Value = vbNullString Then

Me.Text26.Value = Text26.Value + Chr(34) & "OR" & Chr(34) & Me.Combo22.Value

Else
Me.Text26.Value = Me.Combo22.Value
End If
End Sub
Private Sub Command4_Click()
DoCmd.RunSQL "SELECT * INTO tblreplace From tblmain Where [Grade Desc]in(forms!frmreplace!text26);"
End Sub

 

[The_Doc_Man]

That SQL will only work if the exact description exists in both places. You want to do your thing if any component of the list exists in the text box.

I believe you need to more closely read the help associated with the IN operator - and while you are at it, read up on InStr$ function as a way to locate substrings.

 

[RuralGuy]

Try this coding and Doc's suggestions.

Private Sub Command28_Click()

   If Not Me.Text26.Value = vbNullString Then
      Me.Text26 = Me.Text26 & Chr(34) & " OR " & Chr(34) & Me.Combo22
   Else
      Me.Text26 = Me.Combo22
   End If
   
End Sub

Private Sub Command4_Click()

   Dim strSQL As String
   strSQL = "SELECT * INTO tblreplace From tblmain Where " & _
            "[Grade Desc] IN (" & Me.Text26 & ");"
   DoCmd.RunSQL strSQL

End Sub

 

[cmac2210]

Thanks for the help! I was able to figure it out. I guess the query saw the textbox as literal. I got it to work by changing the chr(34) to ' on either side of the items and then passed it to the query as a string, not the textbox. Its working a treat now!