Pass Textbox to Query as Criteria, not as String

mab9

Registered User.
Local time
Today, 15:40
Joined
Oct 25, 2006
Messages
63
I know I've this before but I can't remember exactly how. On a form, I have a textbox (txtPicked) which is populated with a series of numbers froma multi select list box, ie: 5771 or 1000 or 2000

I'm trying to pass this into a query as the criteria but by directly pointing to the forms textbox, its reading it as a string, ie: "5771 or 1000 or 2000"

Since the field is numeric and the criteria is technically a string, it won't return any results. Any ideas?
 
Im' trying to get the criteria into:

Code:
SELECT Min(IIf([Descriptor_Ranking]=0,9999,[Descriptor_Ranking])) AS Rank, tbl_Attributes.Descriptor_Name
FROM tbl_Attributes
WHERE (((tbl_Attributes.Category_ID)='list box results go here'))
GROUP BY tbl_Attributes.Descriptor_Name
ORDER BY Min(IIf([Descriptor_Ranking]=0,9999,[Descriptor_Ranking]));

For the time being, I've put 'list box results go here' where I want the link into the list box selections.
 
Try:
Code:
SELECT Min(IIf([Descriptor_Ranking]=0,9999,[Descriptor_Ranking])) AS Rank, _
 tbl_Attributes.Descriptor_Name
FROM tbl_Attributes
WHERE (((tbl_Attributes.Category_ID)=[COLOR="Red"]Forms!YourFormName.txtPicked[/COLOR]))
GROUP BY tbl_Attributes.Descriptor_Name
ORDER BY Min(IIf([Descriptor_Ranking]=0,9999,[Descriptor_Ranking]));
...using YourFormName of course.
 
Yeah...that works for if the user ends up choosing only 1 categories from the listbox, but they will be choosing multiple. Currently txtPicked is capturing the category ID's in the format of: 1000 or 1200 or 1500

When the query criteria is directly pointed to txtPicked, its reading its contents as a string, ie: "1000 or 1200 or 1500", which won't return any results. I need to treat each of the list box selections individually in the queries criteria.
 
Then you will need to dynamically create the query because the FieldName needs to be repeated each time.
FieldName = 1000 OR FieldName = 1200 or FieldName = 1500
 
It would seem that you want to use the In function but I seem to remember a thread explaining why that was not possible directly, I think it had work rounds but don't know how to find it howver Listers sample might help

here

Brian
 
This sounds like the exact situation I'm in. Does anyone have a good solution? Here is my issue -
I have a list box that I am using to populate a field with the query criteria. I'm even putting in quotes where necessary so that the text box will end up with something like this...

"001144" Or "001117" Or "001099"


If I manually copy and paste the text directly from the textbox into the query criteria, the query works. But referring to the textbox in the criteria as [Forms]![FrmLostItems]![txtHighLines] returns no results unless there is only one string in the textbox (ie "001144"). Why is this? It is the same thing!
Any ideas on this? Here is my code to populate the textbox...


Dim strQuote As String
Dim C
Dim strHighLines As String
strQuote = Chr$(34)
For C = 0 To Me.txtDocumentNumber.ListCount - 1
If Me.txtDocumentNumber.ListCount = 1 Then
strHighLines = Me.txtDocumentNumber.Column(1, C)
Me.txtHighLines = strHighLines
Else
If C = 0 Then
strHighLines = Me.txtDocumentNumber.Column(1, C)
Else
strHighLines = strHighLines & strQuote & " Or " & strQuote & Me.txtDocumentNumber.Column(1, C)
End If
End If
Next C
If Me.txtDocumentNumber.ListCount > 1 Then
If C = Me.txtDocumentNumber.ListCount Then
Me.txtHighLines = strQuote & strHighLines & strQuote
End If
End If


This has been driving me batty so any help would be appreciated. Thanks.
 
Last edited:
Has anyone been able to figure this out? I'm having the same issue.
 
I no longer have access and cannot help, but it is interesting that neither you nor AKdim commented on Lister's solution in the link I posted and why it does not help.

Brian
 

Users who are viewing this thread

Back
Top Bottom