Ivvalid query results

[Marine9925]

I have a table that has the following records...


[txt_PersonalityName] a "Short Text" field


FISD-Mobile-Admin
FISD-Portable-Admin
FISD-Mobile-General
FISD-Portable-General
FISD-Mobile-Security
FISD-Portable-Security


1) I have created a form with a search box allowing the user to enter the search criteria for the query.


2) the query has the following search criteria... [ Like "*" & [Forms]![frm_PersonalitiesList]![txt_PersonalityNameSearch] & "*" ]


Results


If the user enters "F" in the search criteria txt box, all records are returned;


If the user enters "Anything Else", i.e. "FI", "FISD", "G" or "P", no records are returned even thou they are in the table;


Any idea what I am doing wrong?

[June7]

Is this field a Lookup field in table?

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[Welshgasman]

Well you should not have [ and ]around the criteria I would have thought?
Upload a DB with enough data to see the issue.

[CJ_London]

The are a number of ways to filter a form. To your description implies you are modifying the underlying query. If you are modifying the query then you need to requery

It would help us to help you if you provided the code you are actually using - descriptions are a waste of time

[CJ_London]

The are a number of ways to filter a form. To your description implies you are modifying the underlying query. If you are modifying the query then you need to requery

It would help us to help you if you provided the code you are actually using - descriptions are a waste of time

If the user enters "F" in the search criteria txt box, all records are returned

what happens if the user enters”I”?

[Micron]

I would have thought
Like "*'" & [Forms]![frm_PersonalitiesList]![txt_PersonalityNameSearch] & "'*" and not
Like "*" & [Forms]![frm_PersonalitiesList]![txt_PersonalityNameSearch] & "*"

[Welshgasman]

No, I checked what I use in my diabetes DB. Although the value is taken from an input box and passed to a tempvar, this works.

SELECT tblFoods.*
FROM tblFoods
WHERE (((tblFoods.FoodName) Like "*" & [Tempvars]![Food] & "*"));

[June7]

Need apostrophe delimiters when building SQL string in VBA. Unless using RunSQL and embedding full form/control path reference in string. Query object does not require - as shown in the Immediate Window output, Access will supply for the compiled SQL statement.

[Micron]

No, I checked what I use in my diabetes DB

That is basically what I wrote, except I put the single quotes on the wrong side of the asterisks?

[June7]

Welsh is not using apostrophes at all in the query structure. See my previous post.

[Welshgasman]

That is basically what I wrote, except I put the single quotes on the wrong side of the asterisks?

I only used the quotes for the DCount criteria.