Using Like when filtering by form

[jqavins]

Preface: I'm using an Access application that is already written and working, and not under my control. I've tried to find an answer for my problem by Googling, and I can only find answers about creating forms and programming VBA, but these do me no good, as I'm strictly a user. This should be a simple task, and I'm probably making a simple mistake. Also, the application and the data is works with are proprietary, so I will not be able to post all the information that I'm sure we'd all like. I'll be as complete and clear as I can. OK, here we go.

Here's a screenshot of the form, with the key fields highlighted.


4566 records, so I need to filter. I'm looking for records with values that begin with YGBR in the RAMS Item Code field. So, I go to Filter By Form and enter Like "YGBR", and I get to records back. I've also tried Like "YGBR*" with the same result.

Now, what I actually need is records with both YGBR at the start of RAMS Item Code and also not blank in Inv. Item No (bottom left). I can use Is Not Null there and get the results I expect, so I know that filtering this way should work. Based on what little I could find from Googling, it sems that what I'm doing with Like should be working. So what's going on? What am I doing wrong?

[Welshgasman]

And what happens if you rightclick on that control?

[June7]

"and I get to records back" - should that be two or no?

I did a test with that combination of criteria and it works.

Since we can't directly examine your db design, will be hard to determine why it is not working. Perhaps you need to contact the developer.

[jqavins]

And what happens if you rightclick on that control?

Aha! The context menu includes the text filtering I need. That worked. Thank you.

I'd still like to know why Like is not working, but it's no longer vital. I'll wait a little while before marking this as solved, just in case someone can post about the original question. I'll mark it solved tomorrow if not.

Thanks again!

[jqavins]

"and I get to records back" - should that be two or no?

It was "no". (That's what I get for proof reading too fast.)

[jqavins]

Aha! The context menu includes the text filtering I need. That worked. Thank you.

Oh ho, here's something new. After filtering through the context menu, I went into Filter by Form again, and that field was filled in with Like "YGBR%".

I'm no expert; I've used Access before, but not much and not in quite a while. I've never encountered the percent sign this way before. Maybe one of you can make sense of it.

[June7]

What exactly do you mean by "filter by form"? FBF does not have right click context list. If you are not selecting "Filter by Form" under the Advanced filter option on ribbon, then you are NOT using "Filter by Form" and LIKE operator does not work because you were actually changing data in record when you typed that into box. Which tells me combobox is not set for LimitToList Yes. However, no idea why the * was changed to %.

[jqavins]

What exactly do you mean by "filter by form"? FBF does not have right click context list. If you are not selecting "Filter by Form" under the Advanced filter option on ribbon, then you are NOT using "Filter by Form" and LIKE operator does not work because you were actually changing data in record when you typed that into box. Which tells me combobox is not set for LimitToList Yes. However, no idea why the * was changed to %.

When I select FBF I get no good results. When I use the context menu from that field instead of FBF, as Welshgasman suggested, it works.

A coworker here has answered my question completely. The data for this application resides on a separate SQL server, and the server wants the % rather than *. With that, I can mark this thread solved.

Thanks for your help.

[June7]

I have data in SQLServerExpress. I tested FBF using * wildcard and it worked, even on a combobox with lookup. Here is what Access put in the form Filter property from the combobox filter: ((Lookup_cbxPlate.LastN Like "P*"))

Access is pulling data and performing filter, not SQLServer. Recognition of SQLServer wildcard characters has to be activated in Options setting: File > Options > Object Designers > SQL Server Compatible Syntax
Except with pass-through queries which would of course use SQLServer syntax.

Oddly, SQLServer is perfectly happy with * wildcard in the SELECT clause in lieu of field list: SELECT * FROM MyTable;