Can't get filter to work (1 Viewer)

[Kill_Switch]

I have abandonded all hope of me getting this to work by myself and have came to well, here.

I have searched the forums, and have seen many search/filter forms, but I can't seem to get them to work. I have tried http://allenbrowne.com/ser-62.html and just turns into one big fail. Been trying to get this to work for about 16 hours now, with about 4 hours of break time. And I'm about to fall asleep at my chair. As I'm push by work to get this going to save our supply database.


I have a Table with the following info:

And I wish to have a filter, that basically when a user needs to look for a part. All they have to do is enter either a partial Nato Stock Number (we usually use the last 4 #'s) or the part number. And it will filter those results out, displaying all the headers above in the table (NSN, Part, Description, Location)

I really like Allen Brown's method, but I can't for the life of me to get it to work. All I want is the "Contains" box, and the filter button and the reset button, and the results.

Don't know if it's the lack of sleep, or the pressure to get it working, or both.


Thanks all for steering me in the direction in advanced.

 

[MStef]

Try to do it via FILTER BY FORM, (icon).

 

[Galaxiom]

If you are just trying to filter on the partial NSN then Allen Browne's technique is cracking a nut with a sledgehammer.

This kind of problem with searching strings is almost invariably due quote mark errors and the complexity of Allen's code leaves you lots of opportunities to get it wrong.
Did you carefully read his explanation of quotes in quotes? Quoted quotes in VBA is one of the hardest things to write.
http://allenbrowne.com/casu-17.html

For a one field filter you can save yourself a lot of grief with a much simpler where clause.

strWhere = "tablename.[Nato Stock Number] Like ""*" & Forms![formname]![inputtextboxname] & "*"""

or for searching the just the last characters in the field:

strWhere = "tablename.[Nato Stock Number] Like ""*" & Forms![formname]![inputtextboxname] & """"

This will get you going and you can try fancier stuff later.
I assume you understand how to apply a filter with a where clause to a form in VBA.

 

[Kill_Switch]

Thanks Galaxiom for the reply. I am however, a complete new guy here. And just starting out. I basically, need my hand to be taken and shown how to create such a "query" for instance. I have the ambition to learn how from scratch, but I'm on a dead line by my superior officer.

I'm new to code and picking up the layout of it as I go along.

I see this code used in another search. Found here http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=27994&d=1247588792



But I can't seem to get it to work for my table, can someone please help! Also, in the form, how do I make it appear as Nato Stock Number, and not NatoStock Number as the table header?
View attachment Database3.mdb

 

[Kill_Switch]

I forgot to transfer over the hidden unbound box (txtSearch2)from the "Search Multiple Fields" database example and to copy over the "on change" event code.

Private Sub txtSearch_Change()
Dim vSearchString As String

vSearchString = Me.txtSearch.Text
Me.txtSearch2.Value = vSearchString
Me.SearchList.Requery

End Sub

Also here's the code for the data in the form

SELECT Vidmars.CustID, Vidmars.[NatoStock Number], Vidmars.PartNumber, Vidmars.Description, Vidmars.Location
FROM Vidmars
WHERE (((Vidmars.[NatoStock Number]) Like "*" & [Forms]![frmAdvancedSearch1]![txtSearch2] & "*")) OR (((Vidmars.PartNumber) Like "*" & [Forms]![frmAdvancedSearch1]![txtSearch2] & "*")) OR (((Vidmars.Description) Like "*" & [Forms]![frmAdvancedSearch1]![txtSearch2] & "*"))
ORDER BY Vidmars.[NatoStock Number];

SO NOW IT SEARCHES!!!!!! YAY

However, I still would like to know how I make the headers appear correctly in the search results table so each row would read as follows:

Nato Stock Number | Part Number | Description | Location


Also would applying an input mask to the nato stock number field hurt the filter aspect?

 

[Kill_Switch]

However, I still would like to know how I make the headers appear correctly in the search results table so each row would read as follows:

Nato Stock Number | Part Number | Description | Location


Also would applying an input mask to the nato stock number field hurt the filter aspect?

NVM I got it myself.

SELECT Vidmars.CustID, Vidmars.[NatoStock Number] AS [Nato Stock Number], Vidmars.PartNumber AS [Part Number], Vidmars.Description, Vidmars.Location
FROM Vidmars
WHERE (((Vidmars.[NatoStock Number]) Like "*" & [Forms]![frmAdvancedSearch1]![txtSearch2] & "*")) OR (((Vidmars.PartNumber) Like "*" & [Forms]![frmAdvancedSearch1]![txtSearch2] & "*")) OR (((Vidmars.Description) Like "*" & [Forms]![frmAdvancedSearch1]![txtSearch2] & "*"))
ORDER BY Vidmars.[NatoStock Number];