Form criteria does not work with query when not null

[BigRegal]

Hey guys, new to the forum and looking for some direction;



I have a simple query that only references one table ([AssociateMasterT]) that contains column "EmployeeID" and several other columns containing info about the employee. I have a form ([CardCriteriaF]) with combobox field ([EmployeeIDCmBo)]. I have put the following criteria in the query under the "employeeID" field but it only seems to work if the combobox is null;

Like "*" & [Forms]![CardCriteriaF]![employeeidCmBo] & "*"

If i run the query directly ,without the form, it prompts me for the "employeeidcmbo" information, and will work either null or not null. When i run it with the form, it will only work when null.

For some history, done this several times before and have no idea why this particular one will not work. I recently upgraded from Access 2010 to Access 2016, incase that matters. I've also gone as far as deleting the queries and the forms and starting from scratch with no avail. I've also tried with different criteria's pointing to the form, doesn't seem to work.

Thanks in advance

[aytee111]

Is the employee id the first column in the combobox?

[BigRegal]

Yes, the combo box only references one column in the table. As info, the "employeeID" column in the table is the 4th column.

Actually, it references the DB key as well, but thats by default.

[aytee111]

The employee id is a text field?

[BigRegal]

The employee id is a text field?

It is set to "short text"

[aytee111]

Yes, the combo box only references one column in the table. As info, the "employeeID" column in the table is the 4th column.

Actually, it references the DB key as well, but thats by default.

Not sure what you mean about it referencing the DB key as well? In a combobox that is usually the first column.

[BigRegal]

Not sure what you mean about it referencing the DB key as well? In a combobox that is usually the first column.

It is in the first column but it is hidden.. i believe it automatically looks at the table key for reference.

[aytee111]

So back to my question - is the employee id the first column in the combobox? It sounds like it is not, so your criteria is looking at the wrong column.

[BigRegal]

So back to my question - is the employee id the first column in the combobox? It sounds like it is not, so your criteria is looking at the wrong column.

The first field in the cmbo is "EmployeeDBID" (which is the table key, the wizard automatically inserts this) and the second column is the "EmployeeID".
Explain.... the criteria tells it to look at the "employeeID" field.

Like "*" & [Forms]![CardCriteriaF]![employeeidCmBo] & "*"

[aytee111]

The control "employeeidCmBo" is a combobox with a row source which is a query. This query is pulling in multiple columns, when you refer to the combobox without a column number then the first column is assumed. When you say it is "hidden", that is referring to the visual aspect on the form, it is a property of the text box and has nothing to do with the actual data that is in the combobox. Your criteria is referring to the combobox, not to any specific column within it.

Post the SQL of the combobox and we can verify that this is the case.

[BigRegal]

SELECT AssociateMasterT.EmployeeDBID, AssociateMasterT.EmployeeID
FROM AssociateMasterT
ORDER BY AssociateMasterT.[EmployeeID];

[aytee111]

Right, your criteria is looking at the EmployeeDBID field, not the EmployeeID field. Queries do not allow you to look at any other column except the first one, unfortunately. You are using Like which is saying that the user can enter a partial employee ID, but then you get the actual key field so I assume it should be =. In which case you can put the criteria in the key field in the query instead of in the employee id. Or you can remove the key field from the combobox, or you can switch the order in the row source and put the ID first, but be careful of the bound field if you do that.

There are a number of other options here if you cannot use the first column of the combobox in your criteria: https://www.pcreview.co.uk/threads/r...query.2267510/

[BigRegal]

I've tried an = criteria as well and i am still getting the same result, no records. I believe the criteria itself is finebecause when i run the query without the form, it prompts me for the EmployeeID, and it pulls records as expected. Also, i'm using that "like" criteria statement in several other areas in my database that all reference the "associateMasterT" table and it pulls records as expected.

[BigRegal]

Welp, after 8 hours of trying to figure this one out. It magically started working...story of my life.

Thanks for your help!

[jwhite]

Hard to believe it magically started working....

As aytee111 alluded to, if the PK is the first column of the Combo Box and EmployeeID is the second column, then your statement should look like: Like "*" & [Forms]![CardCriteriaF]![employeeidCmBo].Column(1) & "*"