Like Operator Issues

[April15Hater]

Hey guys. I'm running into a problem working with the LIKE operator in one of my queries that is defining a rowsource for a listbox on a form. I think this is a fairly simple thing, but I can't figure out how to make it work right.

Then entire SQL command is below, but here is the clause in question:

(tblProductionTracking.TrackingNumber) Like "*" & [Forms]![frmProductionStep3b]![txtTracking] & "*"

I think what I'm trying to do is best explained by an example. The listbox containing the SQL will be displaying tracking numbers that look like:
123-123
123-124
124-123
175-987
175-988
175-989
181-175
181-995
195-951

In txtTracking, I want to be able to type in say 175 and it only display 175-988 and 175-989. As it is, it filters really haphazardly. It returns anything that has a 1, 7, or a 5. Any help guys? I do have it set to requery on change in txtTracking, but I don't know if my code is off or what.

SELECT tblProductionTracking.ProductionTrackingID, tblProductionTracking.ProductionID, tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber
FROM tblProductionTracking
WHERE (((tblProductionTracking.ProductionID)<>[Forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionTracking.FunctionTrackingID)=[Forms]![frmProductionStep3b]![cboFunctionTrack]) AND ((tblProductionTracking.TrackingNumber) Like "*" & [Forms]![frmProductionStep3b]![txtTracking] & "*" And (tblProductionTracking.TrackingNumber) Not In (SELECT tblProductionTracking.TrackingNumber FROM tblProductionTracking WHERE (((tblProductionTracking.ProductionID)=[forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionTracking.FunctionTrackingID)=[forms]![frmProductionStep3b]![cboFunctionTrack])))))
ORDER BY tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber;

 

[DCrake]

If you want to build a like mask you can perform it many ways

Like Operator Example
This example uses the Like operator to compare a string to a pattern.

Dim MyCheck
MyCheck = "aBBBa" Like "a*a" ' Returns True.
MyCheck = "F" Like "[A-Z]" ' Returns True.
MyCheck = "F" Like "[!A-Z]" ' Returns False.
MyCheck = "a2a" Like "a#a" ' Returns True.
MyCheck = "aM5b" Like "a[L-P]#[!c-e]" ' Returns True.
MyCheck = "BAT123khg" Like "B?T*" ' Returns True.
MyCheck = "CAT123khg" Like "B?T*" ' Returns False.

In your example you are using anywhere in field comparison if you only want to use the prefix then drop the * from the front of the syntax.

David

 

[April15Hater]

No dice... but that was a really informative post, thanks! It has been printed and placed in my reference file for future use!

Diff example, typing 49 in txtTracking now returns
481-527
487-480
487-481
488-524
488-525
489-518

but fails to return 496-515, 498-558, 499-534

SELECT DISTINCT tblProductionTracking.ProductionTrackingID, tblProductionTracking.ProductionID, tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber, tblProductionTracking.SourceData, tblProductionInput.Status FROM tblProductionInput INNER JOIN tblProductionTracking ON tblProductionInput.ProductionID=tblProductionTracking.ProductionID WHERE (((tblProductionTracking.ProductionID)<>[Forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionTracking.FunctionTrackingID)=[Forms]![frmProductionStep3b]![cboFunctionTrack]) AND ((tblProductionTracking.TrackingNumber) Like [Forms]![frmProductionStep3b]![txtTracking] & "*" And (tblProductionTracking.TrackingNumber) Not In (SELECT tblProductionTracking.TrackingNumber FROM tblProductionTracking WHERE (((tblProductionTracking.ProductionID)=[forms]![frmProductionStep3b]![txtProductionID]) AND ((tblProductionTracking.FunctionTrackingID)=[forms]![frmProductionStep3b]![cboFunctionTrack])))) AND ((tblProductionTracking.SourceData)=False) AND ((tblProductionInput.Status)=1)) ORDER BY tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber;

 

[April15Hater]

I also just noticed that when I completely take it out, it returns nothing.

 

[April15Hater]

Just another observation I noticed as I'm trying to get this to work. When I do something to cause the list box to requery it works perfect, so is OnChange the right event trigger for what i'm trying to do?

 

[April15Hater]

Ok, just figured out the problem, but I still don't know how to make it work.

The problem is that I can't have the focus set in the text box i'm using to search when it does the requery. That explains why when I use another object to cause it to requery it works fine.

So does anybody know of a workaround for this?