Query criteria set by values in an unbound textbox 3

[Jennpen1]

The following code takes the items selected in a listbox (PARTNOSELECT) on a form (START_UP) and puts them in a textbox (Text1). It places them into the textbox the following way: "PartNumber" OR "PartNumber" Or etc....


Private Sub Command19_Click()
Dim varRow As Variant
Dim strText As String

strText = ""
For Each varRow In Me!PARTNOSELECT.ItemsSelected
If strText = "" Then
strText = """" & Me!PARTNOSELECT.Column(0, varRow) & """"
Else
strText = strText & " Or """ & "" & Me!PARTNOSELECT.Column(0, varRow) & """"
End If
Next varRow

Text1.Value = strText


If strText <> "" Then
Text1.Value = strText
End If

Exit Sub
End Sub


I am then trying to run a query where the criteria is set to this textbox (Text1). This is what I have in the criteria part of the query:

=[Forms]![START-UP]![Text1]

Everytime I run it, the query comes back blank. I cannot figure out what is causing this problem. Is what I am doing even possible and the problem is due to the formatting of the data??? Any help would be appreciated!!!!!

Jennifer

 

[lespaul]

So what you are saying is when your query is finished it looks like:

SELECT * FROM TABLE WHERE SOMETHING = 'PARTNUMBER OR PARTNUMBER OR PARTNUMBER OR PARTNUMBER'

you can't do it like that.

Try instead to have your calculated string by a comma separated list:

partnumber, partnumber, partnumber

and change your query to

SELECT * FROM TABLE WHERE SOMETHING IN (Text1)



Leslie

 

[Jennpen1]

This works only if only one thing is selected in the Multi Select listbox. If more than one thing is selected the query comes up blank.

Any suggestions as to why??

 

[lespaul]

Have you stepped through the code to make sure you are concatenating the text1 field correctly?

You should have changed your code to:

Private Sub Command19_Click()
Dim varRow As Variant
Dim strText As String

strText = ""
For Each varRow In Me!PARTNOSELECT.ItemsSelected
   If strText = "" Then
      strText = Me!PARTNOSELECT.Column(0, varRow)
    Else
      strText = strText & ", " & Me!PARTNOSELECT.Column(0, varRow)
   End If
Next varRow

Text1.Value = strText


If strText <> "" Then
   Text1.Value = strText
End If

Exit Sub
End Sub

so that the string is created like:

1, 2, 4, 6

In the criteria of your query you should have something like:

IN ([Forms]![START-UP]![Text1])

What is the SQL for your query?




Leslie

 

[Jennpen1]

I tried your code and it still will not work if more than one thing is selected in the listbox. It works perfect if only one things is selected.

I probably should have said this earlier, and I am not sure if this is what is causing the problem but the part numbers (in the listbox) are both numbers and letters and characters. i.e T-1377/APG-65

I am not familiar enough with the In function to know what its data type is. Could this be the problem??

The entire SQL is quite long, but if you want to see it all let me know. It is a select query with groupings and summation. The following is the where and group by clauses:


WHERE ((([A - PART NUMBER DATE TABLE].PARTNO) In ([Forms]![START-UP]![Text1])))
GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO

This is becomign so frustrating!!!

 

[lespaul]

Since you are searching for strings you will need to surround them with quotes:

Private Sub Command19_Click()
Dim varRow As Variant
Dim strText As String

strText = ""
For Each varRow In Me!PARTNOSELECT.ItemsSelected
   If strText = "" Then
      strText = """" & Me!PARTNOSELECT.Column(0, varRow) & """"
    Else
      strText = strText & ", """ & Me!PARTNOSELECT.Column(0, varRow) & """"
   End If
Next varRow

Text1.Value = strText


If strText <> "" Then
   Text1.Value = strText
End If

Exit Sub
End Sub

Again, have you stepped through the code and checked the value of strText, or can you see the value in Text1? Is it correct? Does it list all the selected parts? Now you may run into problems since your data has special characters in it. Is there some other field that you could search by instead? Like an autonumber PK or something like that? Then you could add that information to the list select box and pass it in instead of your string.





Leslie

 

[Jennpen1]

I am beginning to think I might have to approach this another way.

I added the quotation marks and it is doing the same thing--it won't run if more than one thing is selected. I stepped through the code and everything is running as it should. The strings all seem to be correct. The problem is in the transfer of the data from Text1 to the criteria in the query.

Just to test it out I copied and pasted information that was generated and put into Text1 into the In statement of the query. In that case, the query worked out fine. Does this make any sense to you?? I am dumbfounded that 1) why the query won't read the data from the text box and 2) someone else has never had this issue before??

If you do not have anymore insight as to what the problem may be I will try your latter suggestion of using another field. That is going to take quite a bit of effort so I would like to avoid it if possible.

Jennifer

 

[lespaul]

Check out:

FAQ703-3936
Thread701-885370
Thread701-814033

others Search keywords = multiselect query



Leslie

 

[dhookom]

Jennifer,
Use your code to build the delimited list of values (strText) so that it ends up similar to "2,1,22,345" or "'red','white','blue'".

Then use code like:

Dim strSQL as String
Dim strText as String 'delimited list
strText ="...."
strSQL = CurrentDb.QueryDefs("qYourQueryName").SQL 
strSQL = Left(strSQL, Instr(strSQL, " WHERE ") & _
   " WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & _
   strText & ") GROUP BY GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"
CurrentDb.QueryDefs("qYourQueryName").SQL = strSQL

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

that was part of my problem, I was putting " " around each string!

You're the man, Duane!!!

have a star on me!

Have a great weekend!

Leslie

 

[Jennpen1]

I think there is a closing parenthesis missing in the following statement:

strSQL = Left(strSQL, Instr(strSQL, " WHERE ") & _
" WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & _
strText & ") GROUP BY GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

Where does it go?? I tried several places and in each place that I tried I kept getting the following error:

Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', SELECT', or 'UPDATE'

Do you know what the problem could be??

Jennifer

 

[dhookom]

I think I missed this plus had an extra GROUP BY:
strSQL = Left(strSQL, Instr(strSQL, " WHERE ")[red])[/red] & _
" WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & _
strText & ") GROUP BY [red]GROUP BY[/red] [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jennpen1]

I am still having the same problem (same error message).

This is what my code looks like now:

Private Sub Command19_Click()
Dim varRow As Variant
Dim strText As String
Dim strSQL As String

strText = ""
For Each varRow In Me!PARTNOSELECT.ItemsSelected
If strText = "" Then
strText = "" & Me!PARTNOSELECT.Column(0, varRow) & ""
Else
strText = strText & ", " & Me!PARTNOSELECT.Column(0, varRow) & ""
End If
Next varRow

strText = """" & strText & """"

strSQL = CurrentDb.QueryDefs("MASTER RELIABILITY").Sql
strSQL = Left(strSQL, InStr(strSQL, " WHERE ")) & " WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

CurrentDb.QueryDefs("MASTER RELIABILITY").Sql = strSQL

The last line is the one in which the error occurs.

What should I put in the query MASTER RELIABILITY? Since it is a where statement, if there is nothing in the criteria then the entire column dissapears.

 

[dhookom]

You need to understand how to trouble-shoot which is generally quicker and more reliable than asking others. Place a line prior to the "CurrentDb.QueryDefs..."

   Debug.Print strSQL
   CurrentDb.QueryDefs("MASTER  RELIABILITY").Sql = strSQL

If you have a problem with the code, open the debug window by pressing Ctrl+G. Your SQL should be showing in the debug window for you to copy and paste into the sql property of a new, blank query. You might even see the error before doing this.

If you still don't find the error, copy and paste the results from the debug window into a reply.

Using break points, debug.print, and MsgBox strSQL are part of my general trouble-shooting routine.




Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

In the bolded section below:

strSQL = CurrentDb.QueryDefs("MASTER  RELIABILITY").Sql
strSQL = Left(strSQL, [b]InStr(strSQL, " WHERE ")[/b]) & " WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

does that return the end of the location of " WHERE " or the beginning? The reason I ask, is you are also adding a "WHERE". Are you getting two WHERE's in your built query?



Leslie

 

[Jennpen1]

I apologize for my ignorance. I am unfamiliar with the QueryDefs function and I think that is why I am having a hard time finding the problem--I do not know what is supposed to be doing.

The Where statement is not duplicating. This is what shows up during debugging:

WHERE [A - PART NUMBER DATE TABLE].PARTNO In ("131000-19, 3525011-150") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO

Is the QueryDefs adding this line to the already existing MASTER RELIABILITY query?? I guess I do not even understand what the error means-- "expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', 'UPDATE'

I am sorry that I am having so much trouble with this.

 

[lespaul]

your strings need to be ENCLOSED in quotes:

("131000-19", "3525011-150")

change this:

For Each varRow In Me!PARTNOSELECT.ItemsSelected
   If strText = "" Then
      strText = "" & """" & Me!PARTNOSELECT.Column(0, varRow) & """"
    Else
      strText = strText & ", """ & Me!PARTNOSELECT.Column(0, varRow) & """"
   End If
Next varRow

Leslie

 

[Jennpen1]

I have good news and bad news.

Good news is that when I copy the output for strSQL:

WHERE [A - PART NUMBER DATE TABLE].PARTNO In ("131000-19", "3525011-150")

into the query's actual SQL statement, the query works. This is the first time that The query has worked with more then one value in the In statement.

The bad news is that I am still getting the same error.

Why oh why cannot this not just work??!!!!

 

[dhookom]

What happened to the beginning of the query? Shouldn't strSQL be something like:
SELECT...FROM.... WHERE....
I expect that your query [MASTER RELIABILITY] might not have a WHERE clause to begin with. You can either add a dummy one "WHERE 1=1" or change your code:

strSQL = CurrentDb.QueryDefs("MASTER  RELIABILITY").Sql
strSQL = Left(strSQL, InStr(strSQL & " WHERE ", " WHERE ")) & " WHERE [A - PART NUMBER DATE TABLE].PARTNO In (" & strText & ") GROUP BY [A - PART NUMBER DATE TABLE].YEAR_MONTH_CO"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

Maybe you need single ticks?

For Each varRow In Me!PARTNOSELECT.ItemsSelected
   If strText = "" Then
      strText = "" & "'" & Me!PARTNOSELECT.Column(0, varRow) & "'"
    Else
      strText = strText & ", '" & Me!PARTNOSELECT.Column(0, varRow) & "'"
   End If
Next varRow

Leslie