Cannot Use Query Criteria From text Box?

[Gscrot]

Hi

Apologies if this is a stupid question but I am really struggling with the following.

I have the following query

[sql] SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, STOCK]![COST]*[spec]![QTY] AS Total INTO New_Order_2019
FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref
WHERE (((spec.HOUSE_TYPE)=[Forms]![Order_Pad_Builder]![HouseType]) AND ((spec.SHEET)=[Forms]![Order_Pad_Builder]![Txt_WhereStr])); [/sql]

The text box contains a string of choices from a list box control. But the query returns no data. yet if I copy and paste the data from the text box directly into the criteria box of the query builder it produces data??

Any idea what I am doing wrong.

 

[Gasman]

Examine the sql after you paste the criteria.

Access probably used IN ()

Hi

Apologies if this is a stupid question but I am really struggling with the following.

I have the following query

[sql] SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, STOCK]![COST]*[spec]![QTY] AS Total INTO New_Order_2019
FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref
WHERE (((spec.HOUSE_TYPE)=[Forms]![Order_Pad_Builder]![HouseType]) AND ((spec.SHEET)=[Forms]![Order_Pad_Builder]![Txt_WhereStr])); [/sql]

The text box contains a string of choices from a list box control. But the query returns no data. yet if I copy and paste the data from the text box directly into the criteria box of the query builder it produces data??

Any idea what I am doing wrong.

 

[Minty]

What is the format of the string on the form, can you paste up a copy/example ?

 

[The_Doc_Man]

This SELECT...INTO sequence MIGHT work if you tried to execute it via DoCmd.RunSQL but it will NOT run from CurrentDB.Execute - and that is because those two methods of SQL action-query execution operate in different environments. When you copy/paste the criteria into the query builder, you are in the Access GUI. By any chance, when you tried to execute the SQL, did you try to do so using the .Execute method?

SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, STOCK]![COST]*[spec]![QTY] AS Total INTO New_Order_2019
FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref
WHERE (((spec.HOUSE_TYPE)=[COLOR="red"][Forms]![Order_Pad_Builder]![HouseType][/COLOR]) AND ((spec.SHEET)=[COLOR="red"][Forms]![Order_Pad_Builder]![Txt_WhereStr][/COLOR]));

The references in RED are only available in the context of the Access GUI (where the forms and query-builder grid reside). The DoCmd.RunSQL executes in that environment first - but CurrentDB.Execute ONLY operates in the DB Engine environment, which is separate from the Access GUI environment.

The two resolutions would be (a) use DoCmd.RunSQL to run the action query, or (b) pre-build the string to use SUBSTITUTION methods to get the values from the form before you try to execute anything. Then if you attempted the .Execute method, it might work.

SQLString = "SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, STOCK]![COST]*[spec]![QTY] AS Total INTO New_Order_2019
FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref
WHERE (((spec.HOUSE_TYPE)='" & [Forms]![Order_Pad_Builder]![HouseType] & "') AND ((spec.SHEET)='" & [Forms]![Order_Pad_Builder]![Txt_WhereStr] & "'));"

Then you could execute the SQL in either environment. Watch out for having apostrophy and double-quote together, which can be hard to read.

 

[Gscrot]

HI. Thanks for the rapid responses.

This is the code I have addapted to get the multiple select list box items into a string. The IN clause is then included in the string and it is put in the StrClean variable I then set that as the value fo the text box I want to filter the query with. I do execute the SQL query through the docmd.runsql method.

The string currently contains this text In ("Ensuite1","FINAL","Fit1")

Private Sub Command24_Click()

'On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   
    Dim varItem As Variant      'Selected items
    Dim strWhere As String       'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    Dim ListFilter As String
    Dim hty As String
    Dim sql_temp_table As String
    Dim strPath As String
    Dim StrClean As String
      
    strDelim = """"            'Delimiter appropriate to field type. See note 1.
    strDoc = "all report"

'Form!OPB_Sub.Form.Visible = True

    'Loop through the ItemsSelected in the list box.
    With Forms![Order_Pad_builder]![Sheet_List]
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
               ' strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "," And [House_Type] = " & Forms![Order_Pad_Builder]![House Type].value  & """
               strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
               StrClean = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'strWhere = strWhere(House_Type = """ & Me.House_Type & """)
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
            Next
          
        End With
           SQLSub_Order = " SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, [STOCK]![COST]*[spec]![QTY] AS Total FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref WHERE (((spec.HOUSE_TYPE)=[Forms]![Order_Pad_Builder]![HouseType]));"

Forms![Order_Pad_builder]![OPB_Sub].Form.RecordSource = SQLSub_Order
 
     'Remove trailing comma. Add field name, IN operator, and brackets.
        
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
   StrClean = "In (" & Left$(strWhere, lngLen) & ")"
        strWhere = "[SHEET] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "SHEET: " & Left$(strDescrip, lngLen)
                      
             Forms![Order_Pad_builder]![OPB_Sub].Form.Filter = strWhere
             Forms![Order_Pad_builder]![OPB_Sub].Form.FilterOn = True
            
             Me.Txt_WhereStr = StrClean
          
            sql_temp_table = "SELECT spec.HOUSE_TYPE, spec.SHEET, spec.STOCK_CODE, spec.QTY, STOCK.STOCK_DESC, STOCK.Quotation_Ref, STOCK.Merchant_Code, STOCK.Manufacturer_Code, STOCK.COST, [STOCK]![COST]*[spec]![QTY] AS Total INTO New_Order_2019 FROM (STOCK RIGHT JOIN spec ON STOCK.STOCK_CODE = spec.STOCK_CODE) LEFT JOIN Merchant_Quotes ON STOCK.Quotation_Ref = Merchant_Quotes.Quotation_Ref WHERE (((spec.HOUSE_TYPE)=[Forms]![Order_Pad_Builder]![House Type]) AND ((spec.SHEET)='" & StrClean & "'));"
      
             DoCmd.RunSQL sql_temp_table
           
          End If
   
End If

End Sub

 

[isladogs]

The last post was moderated. Posting now to trigger email notifications

I also added code tags to make it more readable