Like " text box value " SQL

[tcox]

I'm having difficulty getting access to accept a value in a txtbox for the like parameter in a query.
This relates to my scrapped use of multi value fields. I'm now entering capsule size separated by spaces for my tooling.
Ex. 00 0 2 4 is entered in the size column for a particular piece of tooling.


the above tooling works for 00, 0, 2 and 4 size capsules. I want to query tooling that size contains 00.

online help only shows tying in Like "00" in the criteria field without mention of using it with form objects

I'm having trouble formulated the Like statement in design view and/or SQL view.
Like "Forms![Navigation_Main]![NavigationSubform].Form![txt_capSize]"
Like '" & Forms![Navigation_Main]![NavigationSubform].Form![txt_capSize] & "'
Like "Me.txtCapSize.value"
none of these and many more do not work

What is the proper way to write an SQL query to search from input from a text box?

-Tevis

[alansidman]

Try this

strSql = "Like Forms![Navigation_Main]![NavigationSubform].Form![txt_capSize] & ""*"""


Here is a link that should help in the future.

http://allenbrowne.com/ser-71.html

[tcox]

I'm unsure how to integrate your suggestion into the SQL statement.

Similar to this?
SELECT T_Part_Sets.Part_Set, T_Part_Sets.Part_Sets_ID
FROM T_Part_Sets
GROUP BY T_Part_Sets.Part_Set, T_Part_Sets.Part_Sets_ID, T_Part_Sets.Machine_Type, T_Part_Sets.Size, T_Part_Sets.Status
HAVING (((T_Part_Sets.Machine_Type)=[Forms]![Navigation_Main]![NavigationSubform].[Form]![txt_machineType]) AND ((T_Part_Sets.Size) Like [Forms]![Navigation_Main]![NavigationSubform].[Form]![txt_capSize] & ""*"") AND ((T_Part_Sets.Status)="IN"))
ORDER BY T_Part_Sets.Part_Set;

[tcox]

SELECT T_Part_Sets.Part_Set, T_Part_Sets.Part_Sets_ID
FROM T_Part_Sets
GROUP BY T_Part_Sets.Part_Set, T_Part_Sets.Part_Sets_ID, T_Part_Sets.Machine_Type, T_Part_Sets.Size, T_Part_Sets.Status
HAVING (((T_Part_Sets.Machine_Type)=[Forms]![Navigation_Main]![NavigationSubform].[Form]![txt_machineType]) AND ((T_Part_Sets.Size) Like ([Forms]![Navigation_Main]![NavigationSubform].[Form]![txt_capSize] & "*")) AND ((T_Part_Sets.Status)="IN"))
ORDER BY T_Part_Sets.Part_Set;

wrapping it in parentheses worked.

[alansidman]

Glad to hear you have it working. Did you read Allen Browne's description on how to handle this going forward with the converter he has designed.