Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

use in statement in access query

Posted on 2016-09-02
17
Medium Priority
?
105 Views
Last Modified: 2016-09-08
I am trying to run an Access query by using value from a text box from a form as a parameter

in the query i have an employee field and I am putting my parameter as
[Forms]![frm_test]![Text4]  and it does not return anything. text in text4 is  

 in ( "Brian Sasso","Robert Sayour","Michael Cuccia",)

.
If i copy and paste the value from textbox into the query it runs ok and returns everything like how i want it to be.

what am i doing wrong. why isn't the query reading value from the text box

also i have tried putting this in the parameter

in ([Forms]![frm_test]![Text4] )

and in that case value of my textbox is
  "Brian Sasso","Robert Sayour","Michael Cuccia"

still it does not return anything. p

can someone please let me know what am I missing
0
Comment
Question by:PratikShah111
  • 6
  • 4
  • 4
  • +2
17 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41782532
this-is-a-SINGLE-string-but-you-probably-know-this-already

in the next row, there is also a SINGLE string:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

and, in the next line there is another SINGLE string
xxxxx,xxxxxxxxxxx,xxxxxxxxxxx,xxxxxxx,xxxxx,xxxxxxxxxxxxx

and, in the next line there is another SINGLE string
"xxxxx","xxxxxxxxxxx","xxxxxxxxxxx","xxxxxxx","xxxxx","xxxxxxxxxxxxx"

It does not matter how many quotes or commas you put into it

[Forms]![frm_test]![Text4] holds ONE STRING ONLY

so, your query is executing that single string like this:

IN(  this-is-a-SINGLE-string-but-you-probably-know-this-already )

but you are expecting:

IN (
       select NAME
       from sometable
        where name =
         OR  name = "Brian Sasso"
         OR name = "Robert Sayour"
         OR name = "Michael Cuccia"
        )

where each name is an individual row of an implied table. But it can't do that because all you have provided is a single string.

& Hopefully you now understand why it does not work as you expect.

To solve have a look at these previous answers:
https://www.experts-exchange.com/questions/23429506/MS-Access-How-to-pass-string-of-values-into-one-parameter-in-an-in-statement.html#a21638124

https://www.experts-exchange.com/questions/27259880/Access-2007-query-How-to-use-a-PARAMETERS-variable-in-an-SQL-IN-clause.html#a36380081
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41782580
Hi.

Try to enclose the names in the textbox between single quotes and separate them with commas. As if you write them in tue query but changing the double quotes with singles.

For example, in the textbox you must write:

 'Brian Sasso','Robert Sayour','Michael Cuccia'

Now your query must be like:
 SELECT [fields] FROM [table] WHERE [Search field] IN ([Forms]![frm_test]![Text4])

Open in new window


Personally, I prefer to build the query in VBA, and later assign the SQL to the query or use it for a recordset, in this case I use
'This line builds the SQL 
    strSQL = "SELECT [fields] FROM [table] WHERE [Search field] IN (" & Form_frm_test.Text4] & ")"

'Now I assign the SQL to a query
    CurrentDb.QueryDefs("MyQuery").SQL = strSQL
    CurrentDb.QueryDefs.Refresh

'If I use it with a recordset
    Set MyRs = Currentdb.OpenRecordset(strSQL)

Open in new window


Best regards.

Antonio.
0
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 41783227
Another approach would be to use a multi-select listbox instead of a textbox, and create the concatenated string in VBA code.  My Access Archon article on Filtering by Multiple Selections shows how to do this.  I am attaching a zip with the article and sample database, and here is a screen shot of the form:
Filtering by multiple selectionsHere is the code:
Private Sub cmdFilter_Click()
'Created by Helen Feddema 10-Aug-2010
'Last modified by Helen Feddema 10-Aug-2010

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lngCount As Long
   Dim lst As Access.ListBox
   Dim strFilter As String
   Dim strData As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strSQL As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   strFilter = ""
   Set lst = Me![lstStates]
      
   'Check that at least one state has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one state"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   
   'Create concatenated filter string
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(0, varItem))
      strFilter = strFilter & "[LocationState] = " & Chr(39) _
         & strData & Chr(39) & " Or "
   Next varItem

   'Trim last "Or" from filter string
   strFilter = Left(strFilter, Len(strFilter) - 4)
   Debug.Print "Filter string: " & strFilter
   
   'Create filtered recordset for other subform
   strQuery = "qryFilteredProjects"
   Set dbs = CurrentDb
   strSQL = "SELECT * FROM tblProjects WHERE " & strFilter _
      & " ORDER BY [LocationState];"
   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   Debug.Print "No. of items found: " & lngCount
   Parent![subFilteredProjects].Form.RecordSource = strQuery
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

accarch197.zip
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:PratikShah111
ID: 41783381
Hi Helena,

This is exactly what I am looking for

just a quick question. once your query is build you get your  "OR"  statement in one row of Criteria
'AL' Or 'AR' Or 'AZ' Or 'CA' Or 'CO'

while for me if I have selected two options from listbox then they get divided in two rows  like  
1
2

if I have more three selected then i have it as
1 or 2
3

I am talking about in the access query that is built after the code deletes the query and re creates it

what do i need to do in order for my entire "OR" statement shows up in one row of the criteria because I have other filters too in the query and since this "OR" statement is divided into two lines the resultset is coming up like not I expect it to be

thanks
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41783398
Try this:

SELECT Employee 
FROM tbl
WHERE (((Eval(Chr(34) & [Employee] & Chr(34) & " " & [Forms]![frm_test]![Text4]))=True));

Open in new window

0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41783572
Hi PratikShah111.

To solve your second question just change
'Create concatenated filter string
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(0, varItem))
      strFilter = strFilter & "[LocationState] = " & Chr(39) _
         & strData & Chr(39) & " Or "
   Next varItem

Open in new window


with:
'Create concatenated filter string
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(1, varItem))
      strFilter = strFilter & "[LocationState] = " & Chr(39) _
         & strData & Chr(39) & " Or "
   Next varItem

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 41783686
If you use the technique in the sample database I posted, you should get a single line with all the selected items -- can you post your code?  (This is VBA code, not query SQL.)
0
 

Author Comment

by:PratikShah111
ID: 41783706
I copied and pasted your code with the changes relates to my db.  here is my code

   Dim dbs As DAO.Database
   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lngCount As Long
   Dim lst As Access.ListBox
   Dim strFilter As String
   Dim strData As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strSQL As String
   Dim strSQL1 As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
    Dim dt1 As Date
    Dim dt2 As Date
    Dim dbsProgram As DAO.Database
    Dim rstTableLinks As DAO.Recordset
    Dim orderstatus As String
   
    orderstatus = "[Status] = 'Order' or [Status] =  'Closed'"

    'first delete everything from total tables
     DoCmd.SetWarnings False
     DoCmd.OpenQuery "qry_delete_totals"
    dt1 = [Forms]![frmReportMenu]![StartDate]
    dt2 = [Forms]![frmReportMenu]![EndDate]
   
     strFilter = ""
   Set lst = Me![lstEmployee]
     
   'Check that at least one state has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one state"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   
   'Create concatenated filter string
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(0, varItem))
      strFilter = strFilter & "[inhouserep] = " & Chr(39) _
         & strData & Chr(39) & " Or "
   Next varItem

   'Trim last "Or" from filter string
   strFilter = Left(strFilter, Len(strFilter) - 4)
   Debug.Print "Filter string: " & strFilter
   
   'Create filtered recordset for other subform
   strQuery = "qry_quote_totals"
   Set dbs = CurrentDb
    strSQL = "Select EmplID,Employee,QuoteNo,grandtotal from tblJobs inner join tblemployees on tbljobs.inhouserep = tblemployees.EmplID where " & strFilter _
    & "  and status = 'Quote' and QuoteDate Between # " & dt1 & " # and # " & dt2 & " #"
   Debug.Print "SQL for " & strQuery & ": " & strSQL
   lngCount = CreateAndTestQuery(strQuery, strSQL)
   
   DoCmd.OpenQuery "insert_quote_totals"
   
   
   
   ' Now lets insert into orders table
    strQuery = "qry_order_totals"
   Set dbs = CurrentDb
    strSQL1 = "Select EmplID,Employee,jobno,grandtotal,i from tblJobs inner join tblemployees on tbljobs.inhouserep = tblemployees.EmplID where " & strFilter _
   & "  and jobno is not null and OrderDate Between # " & dt1 & " # and # " & dt2 & " #"
   Debug.Print "SQL for " & strQuery & ": " & strSQL1
   lngCount = CreateAndTestQuery(strQuery, strSQL1)
   
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
   
   
   
   
   


 rstTableLinks.Close
 
 DoCmd.SetWarnings False
0
 

Author Comment

by:PratikShah111
ID: 41783708
@Helena,
I have not made any changes to your baseutil code. its exactly same as yours.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41783717
@PratikShah111,
Waiting for a feedback on post https:#a41783398 , although you may use whatever works for you.

You may stick to using a query. You may opt to build Text4 field using VBA code, and open the query.
You may expand the work to allow for Like and other variations of search criteria.
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41783786
Hi @PratikShah111.

I'm also waiting your feedback about my posts https://#a41782580 and https://#a41783572.

The first one is a solution for your original post, you wrote:
I am trying to run an Access query by using value from a text box from a form as a parameter

The second link is a solution for yor question:
just a quick question. once your query is build you get your  "OR"  statement in one row of Criteria
'AL' Or 'AR' Or 'AZ' Or 'CA' Or 'CO'

while for me if I have selected two options from listbox then they get divided in two rows  like  
1
2
0
 

Author Comment

by:PratikShah111
ID: 41783865
@hnsar and @Anotonio,

I am still working with Helena to get a perfect solution for my issue so not sure what kind of feedback you want me to give to your posts. Please let me know and I will be happy to do that.

Thanks,
Pratik
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41783869
what kind of feedback
Just apply the query and report if it gets the required output.
0
 

Author Comment

by:PratikShah111
ID: 41783875
ok. then in that case Helena's solution is what is getting me the expected output. Just one minor glitch but that is exactly what I want to happen. thanks
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41783883
ok. then in that case Helena's solution is what is getting me the expected output
Sorry! But required output can be achieved in different approaches.
I only wanted to reward me for spending my time helping you, by just saying if it works in your environment. I tested that in my environment and it works just fine according to your initial question. The working comment for you can be one or more of such comments, and may not be the ultimate solution, as per EE rules.
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41783931
Hi.

Your initial question was using a Textbox, and @hnasr and myself send you correct answers (I also checked hnasr solution).

Before post any solution, I check it, and then I give it. I spent time to give you my support and solution, and you gave me nothing.

ok. then in that case Helena's solution is what is getting me the expected output

Have you checked my solution?.... And have you checked hnasr's solution?...

Helen show you other way, but you must replace the original control, a TextBox with a Listbox. And also, you must add a lot of new code.
0
 

Author Closing Comment

by:PratikShah111
ID: 41789641
thank you for helping out.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question