Passing textbox value into sql query parameter

[gustavoavila]

Hi folks,



I'm having a very simple issue (I think) trying to pass a textbox value as parameter in a sql query.

I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.

This is an example:

The user enter the data:

[Text0]
A1C556CC3C-TNNN
C010070H13

The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"

test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
[Forms]![Search]![Text0].Value = test
DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly

In the SQL code I use the IN operator to find the exact value for each record:

FindPartNo sql query:

SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy
FROM Classifications
WHERE Classifications.PartNumber In ([Forms]![Search]![Text0]);

The problem here is, the query doesn't return results but if I modifiy the query and I put:

WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");

... the query returns the correct results.

What do you think? I'm passing the value incorrect into the sql code?

Thank you for your help!

[rpeare]

Where are you running these slices of code?
Have you run a debug.print SQLCode for the second slice to see if it's actually giving you the value you expect in your IN clause?

[gustavoavila]

The vba code runs when I click on the search button. If you see, the vba code only converts the data pasted in the textbox and excecute a query called FindPartNo (the query is not in the vba code is stored in the database).

I'm running a debug.print in a test sub, with this code:

Private Sub Command146_Click()
SQL = "SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy FROM Classifications WHERE Classifications.PartNumber In (" & [Forms]![Search]![Text0] & ");"
Debug.Print SQL
End Sub

And the debug console returns:

SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code, Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest, Classifications.CreatedBy FROM Classifications WHERE Classifications.PartNumber In ("A1C556CC3C-TNNN","C010070H13");

So, I think the query look good but still not running

[rpeare]

can you provide a sample database with garbage data in it for analysis please. Just create a copy of your db, scrap all the data just leave enough for it to re-create your problem and disguise any data you don't want to share, then compact/repair and upload it to this site.

[gustavoavila]

Sure,

Attached you can find an example.CL codes search tool.zip

[rpeare]

Try adding this field:

Expr1: InStr([forms]![search]![text0],[partnumber])

add a criteria of >0

Remove the criteria from your PARTNUMBER field

[gustavoavila]

I tried this before but I want to return exact matches . With InStr returns the fields but if the data contains another record similar, the query will return both.

For example:

If in the database existe the part number:

12345
123
345

And I only search the part number 12345, the query will return all the matches. Attached screenshot.

[gustavoavila]

Finally I solve the issue creating the query with VBA code and works well. The code is very rude but works

        test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """"
        strSQL = "SELECT Classifications.BU, Classifications.WisperPlantID, Classifications.PartNumber, Classifications.PartDesc, Classifications.US_CL_Code," & _
        "Classifications.MX_CL_Code, Classifications.TARIC_CL_Code, Classifications.COEProject, Classifications.Supplier, Classifications.BrokerRequest," & _
        "Classifications.CreatedBy FROM Classifications WHERE Classifications.PartNumber In (" & test & ");"
        
        
        Set qdf = CurrentDb.CreateQueryDef("FindPartNo", strSQL)
        DoCmd.OpenQuery qdf.Name

Hope someone find this helpful. Thanks for your help rpeare