Solved

use in statement in access query

Posted on 2016-09-02
17
66 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 48

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 500 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
 

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 30

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

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

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 30

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 30

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now