By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

P: 22
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 :)
Jul 19 '07 #1
Share this Question
Share on Google+
23 Replies


P: 22
Sorry but forgot to mention that I am using version 2003.
Jul 20 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
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".
Jul 20 '07 #3

P: 22
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.
Jul 20 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
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.
Jul 20 '07 #5

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

SELECT tblLettersOfCredit.[Date Received], tblLettersOfCredit.[Profit Code], tblLettersOfCredit.[Profit Region], tblLettersOfCredit.ProfitText, tblLettersOfCredit.[LC Number], tblLettersOfCredit.Applicant, tblLettersOfCredit.[Transact Amt], tblLettersOfCredit.[Process Type], tblLettersOfCredit.[Transact Type], tblLettersOfCredit.Rush, tblLettersOfCredit.[Date Needed], tblLettersOfCredit.[Assigned To], tblLettersOfCredit.[Pkg Complete Date], tblLettersOfCredit.[Status Field Ind], tblLettersOfCredit.[Review Date], tblLettersOfCredit.[Issue Date]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.ProfitText) Like "Profits5"))
ORDER BY tblLettersOfCredit.[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 tblLettersOfCredit.[Date Received], tblLettersOfCredit.[Profit Code], tblLettersOfCredit.[Profit Region], tblLettersOfCredit.ProfitText, tblLettersOfCredit.[LC Number], tblLettersOfCredit.Applicant, tblLettersOfCredit.[Transact Amt], tblLettersOfCredit.[Process Type], tblLettersOfCredit.[Transact Type], tblLettersOfCredit.Rush, tblLettersOfCredit.[Date Needed], tblLettersOfCredit.[Assigned To], tblLettersOfCredit.[Pkg Complete Date], tblLettersOfCredit.[Status Field Ind], tblLettersOfCredit.[Review Date], tblLettersOfCredit.[Issue Date]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.[Profit Code]) Like "Misc"))
ORDER BY tblLettersOfCredit.[LC Number];

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


Thanks for your help.
Jul 20 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
The one that works, (data populated originally in table via text field in a form):

SELECT tblLettersOfCredit.[Date Received], tblLettersOfCredit.[Profit Code], tblLettersOfCredit.[Profit Region], tblLettersOfCredit.ProfitText, tblLettersOfCredit.[LC Number], tblLettersOfCredit.Applicant, tblLettersOfCredit.[Transact Amt], tblLettersOfCredit.[Process Type], tblLettersOfCredit.[Transact Type], tblLettersOfCredit.Rush, tblLettersOfCredit.[Date Needed], tblLettersOfCredit.[Assigned To], tblLettersOfCredit.[Pkg Complete Date], tblLettersOfCredit.[Status Field Ind], tblLettersOfCredit.[Review Date], tblLettersOfCredit.[Issue Date]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.ProfitText) Like "Profits5"))
ORDER BY tblLettersOfCredit.[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 tblLettersOfCredit.[Date Received], tblLettersOfCredit.[Profit Code], tblLettersOfCredit.[Profit Region], tblLettersOfCredit.ProfitText, tblLettersOfCredit.[LC Number], tblLettersOfCredit.Applicant, tblLettersOfCredit.[Transact Amt], tblLettersOfCredit.[Process Type], tblLettersOfCredit.[Transact Type], tblLettersOfCredit.Rush, tblLettersOfCredit.[Date Needed], tblLettersOfCredit.[Assigned To], tblLettersOfCredit.[Pkg Complete Date], tblLettersOfCredit.[Status Field Ind], tblLettersOfCredit.[Review Date], tblLettersOfCredit.[Issue Date]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.[Profit Code]) Like "Misc"))
ORDER BY tblLettersOfCredit.[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 (((tblLettersOfCredit.[Profit Code]) Like "Misc"))

To this:
WHERE (((tblLettersOfCredit.[Profit Code]) Like Me!YourCombo.Column(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#.
Jul 20 '07 #7

P: 22
Let me clarify, I am confused. Sorry.

Two tables: tblvalues and tblLetterofCredit
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 tblLetterofCredit.

using your template here:
WHERE (((tblLettersOfCredit.[Profit Code]) Like Me!YourCombo.Column(1))

is this correct?
WHERE (((tblLettersOfCredit.[Profit Code]) Like Me!Pro1Code.Column(9)))

or

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

Thank you for your help.
Jul 20 '07 #8

puppydogbuddy
Expert 100+
P: 1,923
Let me clarify, I am confused. Sorry.

Two tables: tblvalues and tblLetterofCredit
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 tblLetterofCredit.

using your template here:
WHERE (((tblLettersOfCredit.[Profit Code]) Like Me!YourCombo.Column(1))

is this correct?
WHERE (((tblLettersOfCredit.[Profit Code]) Like Me!Pro1Code.Column(9)))

or

or do you mean reference tblValues in the string such as:
WHERE (((tblLettersOfCredit.[Profit Code]) Like Me!tblValues.Pro1Code(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 tblLetterofCredit.>>> 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 tblLettersOfCredit.[Date Received], tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (“tblLettersOfCredit.[Profit Code] Like ” & Me!YourCombo#2Name & “’");
Jul 20 '07 #9

P: 22
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.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.

So, this is all I have really done. Does this makes sense? Thanks for being patient.
Jul 21 '07 #10

puppydogbuddy
Expert 100+
P: 1,923
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.Pro1Code
FROM tblValues
WHERE (((tblValues.Pro1Code) 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 (“tblLettersOfCredit.[Profit Code] Like’” & Me!cboProfitCode.Column(9) & “’”)
Jul 21 '07 #11

P: 22
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 tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (tblLettersOfCredit.[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!
Jul 21 '07 #12

puppydogbuddy
Expert 100+
P: 1,923
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 tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (tblLettersOfCredit.[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 tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (tblLettersOfCredit.[Profit Code] Like ’" & Me!cboProfCode.Column(3) & "'");


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

Let me know what happened.
Jul 21 '07 #13

P: 22
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 tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (tblLettersOfCredit.[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 tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (tblLettersOfCredit.[Profit Code] Like ’ & Me!cboProfCode.Column(3) & "'");

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


So, I next tried this:

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

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

So this, is my current status. And have I mentioned lately that I appreciate your help?
Jul 21 '07 #14

puppydogbuddy
Expert 100+
P: 1,923
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 tblLettersOfCredit.[Profit Code]


FROM tblLettersOfCredit
WHERE (tblLettersOfCredit.[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 tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (tblLettersOfCredit.[Profit Code] Like ’ & Me!cboProfCode.Column(3) & "'");

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


So, I next tried this:

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

which now gives me a syntax error in string query expression for (tblLettersOfCredit.[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.....unless {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 tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[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.Column(3).Value

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

Let me know what happened.
Jul 21 '07 #15

puppydogbuddy
Expert 100+
P: 1,923
Ciara,
See this link for one of the best tutorials on SQL For Access

http://www.fontstuff.com/access/acctut14.htm
Jul 21 '07 #16

puppydogbuddy
Expert 100+
P: 1,923
Ciara,

Best tutorial for MS Access queries:

http://www.fmsinc.com/tpapers/queries/index.html
Jul 21 '07 #17

P: 22
Thanks for the reference.

The answers to the others:

1. Copied and pasted this:

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

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


2. Also, substituted = for Like, and get the same error message.

3. Tried this:

"SELECT tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[Profit Code] Like ’" & Me!cboProfCode.Column(3).Value & "'");

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

4. Tried this:

"SELECT tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[Profit Code] Like ’" & Forms!frmLettersofCredit!cboProfCode.Column(3).Val ue & "'");

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

I not familary with my VB library for missing references; I wil have to research that. Thanks.
Jul 21 '07 #18

puppydogbuddy
Expert 100+
P: 1,923
Thanks for the reference.

The answers to the others:

1. Copied and pasted this:

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

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


2. Also, substituted = for Like, and get the same error message.

3. Tried this:

"SELECT tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[Profit Code] Like ’" & Me!cboProfCode.Column(3).Value & "'");

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

4. Tried this:

"SELECT tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[Profit Code] Like ’" & Forms!frmLettersofCredit!cboProfCode.Column(3).Val ue & "'");

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

I not familary with my VB library for missing references; I wil have to research that. Thanks.
oops, left out ending quotes

"SELECT tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[Profit Code] Like ’" & Forms!frmLettersofCredit!cboProfCode.Column(3).Val ue & "'")";

Just had a thought..I have been giving you VBA syntax for code window; just realized you are using sql view for queries...

Try it like this in query sql view:


"SELECT tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[Profit Code] Like Forms!frmLettersofCredit!cboProfCode.Column(3))";
Jul 21 '07 #19

P: 22
Yes, I am using SQL view in Design Query mode.

In both instances of the two last data strings recommendations, I still get the same error message:
Invalid SQL statement; Expected 'Delete', 'insert', 'procedure', 'select', or 'update'.

Thanks for hanging in there with me. :)
Jul 21 '07 #20

puppydogbuddy
Expert 100+
P: 1,923
Ciara,
Thanks for being patient. Just noticed the gap in the syntax I gave you:
>>>>Column(3).Val ue & "'")";<<<note gap between Val and ue
>>>and also possible syntax error with right parentheses

Try it this way:
"SELECT tblLettersOfCredit.[Profit Code] FROM tblLettersOfCredit WHERE (tblLettersOfCredit.[Profit Code] Like ’" & Forms!frmLettersofCredit!cboProfCode.Column(3).Val ue & "');" <<<<close gap between val and ue

If the above does not work, try working from the query design grid instead of the Sql view. In the criteria row of [Profit Code], put the following:

Like Forms!frmLettersofCredit!cboProfCode.Column(3)

If the above works, copy and post the SQL view so I can see the syntax.
Jul 22 '07 #21

P: 22
I am getting the same error message as before when I put the SQL code via copy/paste. I did change it slightly - since I have removed all my white spaces from my field names in order to lose the brackets such as [profit code] in the code - to simplify things.


When utilizing the Query Design Grid, I put the below string of code - please note I also change the cbo name and made it static with the other naming coventions:

Like Forms!frmLettersOfCredit!cboProfitCode.Column(3)

With this I get the following:
Undefined function 'Forms!frmLettersOfCredit!cboProfitCode.Column' in expression.

Wouldn't I put the value also that I am trying to locate such as "Misc" in this string somewhere?
Jul 22 '07 #22

puppydogbuddy
Expert 100+
P: 1,923
I am getting the same error message as before when I put the SQL code via copy/paste. I did change it slightly - since I have removed all my white spaces from my field names in order to lose the brackets such as [profit code] in the code - to simplify things.

When utilizing the Query Design Grid, I put the below string of code - please note I also change the cbo name and made it static with the other naming coventions:

Like Forms!frmLettersOfCredit!cboProfitCode.Column(3)

With this I get the following:
Undefined function 'Forms!frmLettersOfCredit!cboProfitCode.Column' in expression.

Wouldn't I put the value also that I am trying to locate such as "Misc" in this string somewhere?
Hi Ciara,
<<<<<Wouldn't I put the value also that I am trying to locate such as "Misc" in this string somewhere?>>>> Not if Misc is the <name> of fourth Column from the left (col # 3) in your combo box. if Misc is a specific value in the fourth column, then you would use: Like "Misc" in the criteria row of your query instead of referencing the combo box.

Just had a thought......make sure the "Show" box is checked for each column n the query grid that you want to appear in the query results.

Undefined function is either misspelled object names or a vb reference library problem. Double check object names against their name property. To check vb library references, go to vb editor (code window); go to command menu, select Tools>References and note if any of them say "Missing"

I could probably fix your problen quickly if I had the file in front of me. Could you convert it to Access 2000, remove any sensitive info, and zip/email it to me? You can get my email address by going to my profile and downloading my vCard. Let me know.
Jul 22 '07 #23

P: 22
Misc is a value or an item in the drop-down combo box.

Show is checked for the field in the query. I completely scaled everything down to just two tables and one field in a new db, trying to simplify for troubleshooting, so there is only a possibility of one field to show in my scaled down version.

From what I can tell all the VB libraries are there in the references.

Going to zip to scaled down where you can see what I am trying to convey.

Thank you so much.
Jul 22 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.