Criteria in a Query Being Ignored When a Field is Populated by a Combo Box

**Ciara9** · Jul 20 '07, 03:02 AM

Sorry but forgot to mention that I am using version 2003.

**puppydogbuddy** · Jul 20 '07, 04:43 AM

Originally posted by Ciara9

I have a table of static values that I am acessing from another table to populate combo boxes. Next I have a form where this information is selected by the user and updates directly to the table. Everything works fine. I can create simple queries and reports with no problem as well as update the data. 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, it will completely ignore it. However, if I use the same functionality in a column where a combo box is not used to populate the field, it finds it with no problem.

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 :)

It appears that you may be making your query non-updateable when you add the additional criteria. Open your query by itself and try to update it directly. If you can't, look at the access status bar at the bottom left of the screen. It should say query not updateable. If that is not it, then it would appear to be a syntax error of some kind.

If the query not being updateable is the case, there are a number of remedies that can be applied depending on why it is non-updateable. In either case, in order to try to fix it, you need to post one of the simple queries you say works, and one of the expanded queries that you say "ignores your criteria".

**Ciara9** · Jul 20 '07, 11:23 AM

If you are saying run the query and see if the results are what I am expecting while working in design mode - then when I put in simple criteria such as:

Like "Profits5"

under one of the fields that is typed in by the user, then I get exactly what I expect. If I remove that, and then put the same type of criteria for one of fields that was populated via a combo box:

Like "Misc"

and run the query then I get no results at all. There is nothing that appears at the bottom left hand corner of the screen. Also, I am using a simple Select query created from using the wizard.

**puppydogbuddy** · Jul 20 '07, 11:53 AM

Originally posted by Ciara9

If you are saying run the query and see if the results are what I am expecting while working in design mode - then when I put in simple criteria such as:

Like "Profits5"

under one of the fields that is typed in by the user, then I get exactly what I expect. If I remove that, and then put the same type of criteria for one of fields that was populated via a combo box:

Like "Misc"

and run the query then I get no results at all. There is nothing that appears at the bottom left hand corner of the screen. Also, I am using a simple Select query created from using the wizard.

Ciara,
Ok, please post the SQL of the two sample queries you mentioned above. Thanks.

**Ciara9** · Jul 20 '07, 01:39 PM

The one that works, (data populated originally in table via text field in a form):

SELECT tblLettersOfCre dit.[Date Received], tblLettersOfCre dit.[Profit Code], tblLettersOfCre dit.[Profit Region], tblLettersOfCre dit.ProfitText, tblLettersOfCre dit.[LC Number], tblLettersOfCre dit.Applicant, tblLettersOfCre dit.[Transact Amt], tblLettersOfCre dit.[Process Type], tblLettersOfCre dit.[Transact Type], tblLettersOfCre dit.Rush, tblLettersOfCre dit.[Date Needed], tblLettersOfCre dit.[Assigned To], tblLettersOfCre dit.[Pkg Complete Date], tblLettersOfCre dit.[Status Field Ind], tblLettersOfCre dit.[Review Date], tblLettersOfCre dit.[Issue Date]
FROM tblLettersOfCre dit
WHERE (((tblLettersOf Credit.ProfitTe xt) Like "Profits5") )
ORDER BY tblLettersOfCre dit.[LC Number];

Next, I removed the above to keep it simple.

The one that doesn't work (data populated originally in table via combo box in a form):

SELECT tblLettersOfCre dit.[Date Received], tblLettersOfCre dit.[Profit Code], tblLettersOfCre dit.[Profit Region], tblLettersOfCre dit.ProfitText, tblLettersOfCre dit.[LC Number], tblLettersOfCre dit.Applicant, tblLettersOfCre dit.[Transact Amt], tblLettersOfCre dit.[Process Type], tblLettersOfCre dit.[Transact Type], tblLettersOfCre dit.Rush, tblLettersOfCre dit.[Date Needed], tblLettersOfCre dit.[Assigned To], tblLettersOfCre dit.[Pkg Complete Date], tblLettersOfCre dit.[Status Field Ind], tblLettersOfCre dit.[Review Date], tblLettersOfCre dit.[Issue Date]
FROM tblLettersOfCre dit
WHERE (((tblLettersOf Credit.[Profit Code]) Like "Misc"))
ORDER BY tblLettersOfCre dit.[LC Number];

Both work the same regardless of the ascending option: (Order by Clause).

Thanks for your help.

**puppydogbuddy** · Jul 20 '07, 02:20 PM

Originally posted by Ciara9

The one that works, (data populated originally in table via text field in a form):

SELECT tblLettersOfCre dit.[Date Received], tblLettersOfCre dit.[Profit Code], tblLettersOfCre dit.[Profit Region], tblLettersOfCre dit.ProfitText, tblLettersOfCre dit.[LC Number], tblLettersOfCre dit.Applicant, tblLettersOfCre dit.[Transact Amt], tblLettersOfCre dit.[Process Type], tblLettersOfCre dit.[Transact Type], tblLettersOfCre dit.Rush, tblLettersOfCre dit.[Date Needed], tblLettersOfCre dit.[Assigned To], tblLettersOfCre dit.[Pkg Complete Date], tblLettersOfCre dit.[Status Field Ind], tblLettersOfCre dit.[Review Date], tblLettersOfCre dit.[Issue Date]
FROM tblLettersOfCre dit
WHERE (((tblLettersOf Credit.ProfitTe xt) Like "Profits5") )
ORDER BY tblLettersOfCre dit.[LC Number];

Next, I removed the above to keep it simple.

The one that doesn't work (data populated originally in table via combo box in a form):

SELECT tblLettersOfCre dit.[Date Received], tblLettersOfCre dit.[Profit Code], tblLettersOfCre dit.[Profit Region], tblLettersOfCre dit.ProfitText, tblLettersOfCre dit.[LC Number], tblLettersOfCre dit.Applicant, tblLettersOfCre dit.[Transact Amt], tblLettersOfCre dit.[Process Type], tblLettersOfCre dit.[Transact Type], tblLettersOfCre dit.Rush, tblLettersOfCre dit.[Date Needed], tblLettersOfCre dit.[Assigned To], tblLettersOfCre dit.[Pkg Complete Date], tblLettersOfCre dit.[Status Field Ind], tblLettersOfCre dit.[Review Date], tblLettersOfCre dit.[Issue Date]
FROM tblLettersOfCre dit
WHERE (((tblLettersOf Credit.[Profit Code]) Like "Misc"))
ORDER BY tblLettersOfCre dit.[LC Number];

Both work the same regardless of the ascending option: (Order by Clause).

Thanks for your help.

If "Misc" is a column in your combobox, you are not referencing it correctly.

Try changing this:
WHERE (((tblLettersOf Credit.[Profit Code]) Like "Misc"))

To this:
WHERE (((tblLettersOf Credit.[Profit Code]) Like Me!YourCombo.Co lumn(1))

The column#'s in your combo start with 0 for the first column on the left. Replace YourCombo with the actual name of your combo. Also, if the misc column is not the second column of the combo, change and put the correct column#.

**Ciara9** · Jul 20 '07, 03:38 PM

Let me clarify, I am confused. Sorry.

Two tables: tblvalues and tblLetterofCred it
In my table, tblvalues, in the Column named Pro1Code, the value Misc is listed as the 10th Value. The user selects Misc from the combo box and then it writes to the column or field named Profit Code in tblLetterofCred it.

using your template here:
WHERE (((tblLettersOf Credit.[Profit Code]) Like Me!YourCombo.Co lumn(1))

is this correct?
WHERE (((tblLettersOf Credit.[Profit Code]) Like Me!Pro1Code.Col umn(9)))

or

or do you mean reference tblValues in the string such as:
WHERE (((tblLettersOf Credit.[Profit Code]) Like Me!tblValues.Pr o1Code(9)))

Thank you for your help.

**puppydogbuddy** · Jul 20 '07, 06:44 PM

Originally posted by Ciara9

Let me clarify, I am confused. Sorry.

Two tables: tblvalues and tblLetterofCred it
In my table, tblvalues, in the Column named Pro1Code, the value Misc is listed as the 10th Value. The user selects Misc from the combo box and then it writes to the column or field named Profit Code in tblLetterofCred it.

using your template here:
WHERE (((tblLettersOf Credit.[Profit Code]) Like Me!YourCombo.Co lumn(1))

is this correct?
WHERE (((tblLettersOf Credit.[Profit Code]) Like Me!Pro1Code.Col umn(9)))

or

or do you mean reference tblValues in the string such as:
WHERE (((tblLettersOf Credit.[Profit Code]) Like Me!tblValues.Pr o1Code(9)))

Thank you for your help.

Ciara,
No problem. Now I am confused!!
this statement:
<<< In my table, tblvalues, in the Column named Pro1Code, the value Misc is listed as the 10th Value. The user selects Misc from the combo box and then it writes to the column or field named Profit Code in tblLetterofCred it.>>> You never mentioned a second table called tblValues or that the combo box is used to update the Profit Code in tblLOC.

Let’s see if I can sort this out:

1.Do you have 2 combo boxes? the row source for the combo box described above is a Select * From tblValues <<<not tblL.O.C.>>>> and must have some afterUpdate VBA code to update tblLOC.

2. In simple terms the query#2 you have presented appears to be a select statement that selects the field value in your LC table for the ProfitCode selected from your combo box after the update of tblLOC in #1 above…..so this query must be based on another combo box??? I am going to assume that until I get some clarification from you.

3. If 1 and 2 above are correct, then your original syntax seems to be correct, except that the where clause syntax needs to reference the combo box #2. I am going to assume that the ProfitCode is the bound column of your second combo box.

SELECT tblLettersOfCre dit.[Date Received], tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (“tblLettersOfC redit.[Profit Code] Like ” & Me!YourCombo#2N ame & “’");

**Ciara9** · Jul 21 '07, 12:08 AM

I am sorry that I have confused you, and I am still trying to sort thru your answer. I wish I could do a screen print that would be so much easier. :)

Let me see if I can explain this and I am going to use Profit Code as an example.

TblValues are only 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. No combos defined here.

Then there is tblLOC. Here I have similar field names that matches each field in tblValues where I can populate them in my form. The combo boxes are defined here only in tblLOC. They all are defined as combo boxes, with a row source type of Table/Query and for instance I have the row source displaying as follows for the field Profit Code:

SELECT tblValues.ID, tblValues.Pro1C ode
FROM tblValues
WHERE (((tblValues.Pr o1Code) 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.

So, this is all I have really done. Does this makes sense? Thanks for being patient.

**puppydogbuddy** · Jul 21 '07, 05:56 AM

Ciara,
Ok, it is starting to make sense now. Note that I used the recomended naming convention for the combo box (cboProfitCode) to distinguish it from the underlying field (ProfitCode) to which the selected item from the combo box list is bound. Let me recap the relevant info:
1.two tables, with tblValues used as a lookup table for tblL.O.C.
tblValues has these fields---à ID, Pro1Code
tblL.O.C. has these fields --à ProfitCode, ProfitText
2.one form which will call YourForm since I don’t know the form name
YourForm has tblL.O.C. as its record source
YourForm has the following relevant controls: a combobox

The combo box properties:
Name: cboProfitCode
Control Source: ProfitCode (bound to tblL.O.C. via Lookup)

Row Source:
SELECT tblValues.ID, tblValues.Pro1C ode
FROM tblValues
WHERE (((tblValues.Pr o1Code) Is Not Null));

Based on the above structure, the answer to your question is to change the where clause as shown below. This assumes the name of your combo box is cboProfitCode and that the 10th column from the left (column#9 starting with column 0) contains the code you want selected from tblL.O.C.

WHERE (“tblLettersOfC redit.[Profit Code] Like’” & Me!cboProfitCod e.Column(9) & “’”)

**Ciara9** · Jul 21 '07, 11:50 AM

Yes, the scenario is exactly as stated. I have changed the combo box to cboProfCode to clarify. When I entered the data string in SQL in the Query I got syntax errors. I played around with the quotes marks and finally removed them because no matter what I did, I still got syntax errors. This is what I currently have. Profit Code is the 4th column in tblLOC.

With the string of code below, I am now getting the error message "Undefined function Me!cboProfCode. Column(3) in expression."

SELECT tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & ’);

I am using 2003, and don't know if that matters. Any ideas? Thank you so much for your help!

**puppydogbuddy** · Jul 21 '07, 01:34 PM

Originally posted by Ciara9

Yes, the scenario is exactly as stated. I have changed the combo box to cboProfCode to clarify. When I entered the data string in SQL in the Query I got syntax errors. I played around with the quotes marks and finally removed them because no matter what I did, I still got syntax errors. This is what I currently have. Profit Code is the 4th column in tblLOC.

With the string of code below, I am now getting the error message "Undefined function Me!cboProfCode. Column(3) in expression."

SELECT tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & ’);

I am using 2003, and don't know if that matters. Any ideas? Thank you so much for your help!

I think you are getting close. Let me confirm that you don't have any subforms, only a main form. If this does not work, let me see what the SQL would look like with the values for the fields and controls entered in. Try the syntaxes below.

if Profit Code is text:
"SELECT tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Me!cboProfCode. Column(3) & "'");

if profit code is numeric:
"SELECT tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] = " & Me!cboProfCode. Column(3));

Let me know what happened.

**Ciara9** · Jul 21 '07, 02:17 PM

I do have a subform in my form for comments. And I will try to say this without being misleading. Here it goes. :) So - yes. However, this subform writes to tblcomments. The idea is to pull up an account in frmLOC, enter info in the comments in the subform and write each comment to a new record in the tblComments table. tblComments is then related to another field in tblLOC to pull in all comments into each account displayed in the form, frmLOC, if desired to track activity of calls, etc... There are no combo fields in the subform, frmComments, only the date, time and memo field of the each comment made. So, to me - it really has no influence that I can see on the query since I am working with combo fields that are linked via tblValues and tblLOC. Of course I am out here begging for help, so I may be wrong. :)

To answer your other question, in both tblValues as well as tblLOC, this field is Text. Therefore, I tried this one:

"SELECT tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Me!cboProfCode. Column(3) & "'");

I get, this error message: Invalid SQL statement; expected 'Delete', 'insert', 'procedure', 'select', or 'update'.

So, next I tried the below since the quotes seem to cause problems with this for me:

SELECT tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & "'");

and got this error message: "Undefined function Me!cboProfCode. Column(3)."

So, I next tried this:

WHERE (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & ');

which now gives me a syntax error in string query expression for (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & ');.

So this, is my current status. And have I mentioned lately that I appreciate your help?

**puppydogbuddy** · Jul 21 '07, 03:09 PM

Originally posted by Ciara9

I do have a subform in my form for comments. And I will try to say this without being misleading. Here it goes. :) So - yes. However, this subform writes to tblcomments. The idea is to pull up an account in frmLOC, enter info in the comments in the subform and write each comment to a new record in the tblComments table. tblComments is then related to another field in tblLOC to pull in all comments into each account displayed in the form, frmLOC, if desired to track activity of calls, etc... There are no combo fields in the subform, frmComments, only the date, time and memo field of the each comment made. So, to me - it really has no influence that I can see on the query since I am working with combo fields that are linked via tblValues and tblLOC. Of course I am out here begging for help, so I may be wrong. :)

To answer your other question, in both tblValues as well as tblLOC, this field is Text. Therefore, I tried this one:

"SELECT tblLettersOfCre dit.[Profit Code]

FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Me!cboProfCode. Column(3) & "'");

I get, this error message: Invalid SQL statement; expected 'Delete', 'insert', 'procedure', 'select', or 'update'.

So, next I tried the below since the quotes seem to cause problems with this for me:

SELECT tblLettersOfCre dit.[Profit Code]
FROM tblLettersOfCre dit
WHERE (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & "'");

and got this error message: "Undefined function Me!cboProfCode. Column(3)."

So, I next tried this:

WHERE (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & ');

which now gives me a syntax error in string query expression for (tblLettersOfCr edit.[Profit Code] Like ’ & Me!cboProfCode. Column(3) & ');.

So this, is my current status. And have I mentioned lately that I appreciate your help?

Ciara,
Agreed.....unle ss {Profit Code] is on the subform, the subform is not relevant. the syntax I gave you for text is correct as far as the use of quotes, so if the sql is failing it is failing for reasons other than the placement of quotes. The sql string should look like this prior to being compiled:

"SELECT tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Me!cboProfCode. Column(3) & "'");

1. try it again (use copy and paste); make sure your code is compiled.
2. if it does not work, try replacing Like with =
3. if it still does not work, change >>>> Me!cboProfCode. Column(3)
to>>>> Me!cboProfCode. Column(3).Value

if that does not work, change to >>> Forms!YourForm! cboProfCode.Col umn(3).Value

if that does not work, check your VB Library for "Missing References"

Let me know what happened.