Problem with Criteria in Queries using Combo Boxes

[Ciara9]

Let me see if I can explain this.

I have two tables: TblValues and TblLOC.

TblValues is a table of stactic values where I have listed about nine field names with values under each that usually will not change. This way if there is a change to one of the fields later, I can just add it to the table.

Then there is tblLOC. Here I have similar field names that matches each field in tblValues where I can populate them in my form. I have several fields defined as combo boxes. In the properties for each field defined as a combo box, for row source type has Table/Query selected and for the row source I have the following code in the table, which is an example for the field named Profit Code in TblLOC.

SELECT tblValues.ID, tblValues.Pro1Code
FROM tblValues
WHERE (((tblValues.Pro1Code) Is Not Null));

Then I have my form where I have referenced my fields from the tblLOC. In the properties of each combo box, under the tab labeled DATA, the control source has the name of the the combo box field that is in tblLOC, in this case Profit Code.

Everything works fine. The user selects a value from the form and tblLOC is updated. I can create queries without criteria with no problem. However, in a query if I try to add any criteria, even as simple as using "like" to identify a value in a field of the table that has been populated by the combo box, I will get no results. However, if I use the same functionality in a column where a combo box is not used to populate the field, I get the results that I expect.

Here is an example of the SQL in the query that does not work for me that is applied against a field that is a combo box.

SELECT tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.[Profit Code]) Like "Misc"));


If I use the same functionality with a field that is not populated by one of the combo boxes then everything works fine.

I am hoping that there's a simple solution for this, maybe syntax due to the use of a combo box or maybe a property that I need to set... So far I have been unsuccessful in finding the answer. If anyone has any ideas on this one, I would certainly appreciate it. Thanks

 

[josef_vp]

Are you trying to populate a combo box with another combo box? If not, then try to use the criteria in your query:

Forms![YourFormName].YourComboBoxName.value

Then in the afterupdate property of your combo box, try Me.MyQuerySubform.Requery

change the values

hope this helps

 

[Ciara9]

Thanks for trying to help. No, I am not trying to populate a combo with another combo. Instead, I am populating a table with a combo from a form. The table reflects the value chosen by the user as the default value in the table, as well as the drop down with all the other values in Table design mode. The information is there but in some sort of an array and I am having trouble extracting it. If I do not put any criteria at all, it pulls the values perfectly for each record. But if I put a specific value in such as (Like Misc) in Query Design Mode, then the query field is blank as far as the results go and if you click on the field then there's a drop down with all the data values. If that makes sense.

 

[ajetrumpet]

if I put a specific value in such as (Like Misc) in Query Design Mode, then the query field is blank as far as the results go

Ciara,

Access criteria wildcards are "*" and "?" How about trying "*misc*" instead of just "misc" in the criteria section?? It would explain why you are not seeing any results. Actually, what you are typing in without the wildcards should search for full strings that read "misc" only.

The "*" is used to find a string of characters while the "?" only finds one character.

 

[Ciara9]

Thanks for trying to help. Even using the asterisks in my query, I still do not see any results.

 

[ajetrumpet]

I'd have to see the DB Ciara, I'm not really sure what you're doing...

 

[Ciara9]

I certainly understand. I think that I got it figured. Thank you for helping.