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

[puppydogbuddy]

Ciara,
See this link for one of the best tutorials on SQL For Access

http://www.fontstuff.com/access/acctut14.htm

[puppydogbuddy]

Ciara,

Best tutorial for MS Access queries:

http://www.fmsinc.com/tpapers/queries/index.html

[Ciara9]

Thanks for the reference.

The answers to the others:

1. Copied and pasted this:

"SELECT tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[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 tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[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 tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Forms!frmLetter sofCredit!cboPr ofCode.Column(3 ).Value & "'");

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.

[puppydogbuddy]

Thanks for the reference.

The answers to the others:

1. Copied and pasted this:

"SELECT tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[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 tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[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 tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Forms!frmLetter sofCredit!cboPr ofCode.Column(3 ).Value & "'");

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 tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Forms!frmLetter sofCredit!cboPr ofCode.Column(3 ).Value & "'")";

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 tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[Profit Code] Like Forms!frmLetter sofCredit!cboPr ofCode.Column(3 ))";

[Ciara9]

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

[puppydogbuddy]

Ciara,
Thanks for being patient. Just noticed the gap in the syntax I gave you:
>>>>Column(3).V al ue & "'")";<<<no te gap between Val and ue
>>>and also possible syntax error with right parentheses

Try it this way:
"SELECT tblLettersOfCre dit.[Profit Code] FROM tblLettersOfCre dit WHERE (tblLettersOfCr edit.[Profit Code] Like ’" & Forms!frmLetter sofCredit!cboPr ofCode.Column(3 ).Value & "');" <<<<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!frmLetter sofCredit!cboPr ofCode.Column(3 )

If the above works, copy and post the SQL view so I can see the syntax.

[Ciara9]

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!frmLetter sOfCredit!cboPr ofitCode.Column (3)

With this I get the following:
Undefined function 'Forms!frmLette rsOfCredit!cboP rofitCode.Colum n' in expression.

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

[puppydogbuddy]

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!frmLetter sOfCredit!cboPr ofitCode.Column (3)

With this I get the following:
Undefined function 'Forms!frmLette rsOfCredit!cboP rofitCode.Colum n' 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......ma ke 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>Reference s 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.

[Ciara9]

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.