Cannot Use Query Criteria From text Box? (1 Viewer)

G

Gscrot

New member
Local time
Today, 00:29
Joined
Mar 21, 2019
Messages
2
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

Gasman

Enthusiastic Amateur
Local time
Today, 00:29
Joined
Sep 21, 2011
Messages
14,231
Examine the sql after you paste the criteria.

Access probably used IN ()

Gscrot said:
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.
Click to expand...
 
Minty

Minty

AWF VIP
Local time
Today, 00:29
Joined
Jul 26, 2013
Messages
10,366
What is the format of the string on the form, can you paste up a copy/example ?
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:29
Joined
Feb 28, 2001
Messages
27,138
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?

Code: 
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.

Code: 
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.
 
G

Gscrot

New member
Local time
Today, 00:29
Joined
Mar 21, 2019
Messages
2
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")

Code: 
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
 
Last edited by a moderator:
isladogs

isladogs

MVP / VIP
Local time
Today, 00:29
Joined
Jan 14, 2017
Messages
18,209
The last post was moderated. Posting now to trigger email notifications

I also added code tags to make it more readable
 
You must log in or register to reply here.

Similar threads

N
Can't get query criteria to read a form text field
Replies
9
Views
427
Pat Hartman
P
M
Query for multiple "not like" criteria on long text fields operating as "or", not "and".
Replies
9
Views
346
mike60smart
mike60smart
T
Solved Query no longer recognizes calculated field selected from a text box on a form
Replies
5
Views
392
isladogs
isladogs
G
Solved Using either Global Variable or Form field as query criteria
2
Replies
27
Views
1,369
GinnyR
G
E
query for cascade combo boxes
Replies
4
Views
723
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom