Pass Textbox to Query as Criteria, not as String (1 Viewer)

mab9

Registered User.
Local time
Today, 07:51
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?
 

RuralGuy

AWF VIP
Local time
Today, 06:51
Joined
Jul 2, 2005
Messages
13,826
What does the SQL for your query look like?
 

mab9

Registered User.
Local time
Today, 07:51
Joined
Oct 25, 2006
Messages
63
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.
 

RuralGuy

AWF VIP
Local time
Today, 06:51
Joined
Jul 2, 2005
Messages
13,826
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.
 

mab9

Registered User.
Local time
Today, 07:51
Joined
Oct 25, 2006
Messages
63
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.
 

RuralGuy

AWF VIP
Local time
Today, 06:51
Joined
Jul 2, 2005
Messages
13,826
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
 

Brianwarnock

Retired
Local time
Today, 12:51
Joined
Jun 2, 2003
Messages
12,701
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
 

AKdlm

New member
Local time
Today, 06:51
Joined
Sep 11, 2009
Messages
1
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:

jmaty23

Registered User.
Local time
Today, 08:51
Joined
Jul 24, 2012
Messages
53
Has anyone been able to figure this out? I'm having the same issue.
 

Brianwarnock

Retired
Local time
Today, 12:51
Joined
Jun 2, 2003
Messages
12,701
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

Top Bottom