Row Source Dropdown filter

[pikoy]

Syntax error Help....

I have a form with a combo box that is valued based on a table (Contacts). The table has two four columns: ID, First Name, Last Name and DInteg ( Yes/No ). Trying to filter the values on the combo box to only display First Name and Last Name that has a DInteg value of 'Yes'.

Here is my Row Source Code: - syntax error

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] AS Expr1 FROM Contacts ORDER BY 
Contacts.[First Name] & " " & WHERE IIF ([Forms]![Issues2]![Opened By] = Yes,(Contacts.DInteg);

I also tried DoCmd.ApplyFilter , "[DInteg]<>'Yes' on afterUpdate - not working either

 

[lcook1974]

Could you post a sample DB?

Can you not just set the rowsource for something like:
Select FirstName, LastName From Contacts WHERE Dinteg = 'Yes'

or is it based on text boxes on the form?

Larry

 

[pikoy]

Larry,

First off, Thank you for the reply.

I tried that but its giving me a syntax error ( see image below ). I have double checked the code, maybe i missed something, but as far as i know i didn't. It may just be something so small.

On the Form, the code is in the rowsource of a combo box.

If I only put the code without the " WHERE Contacts.[DInteg] = 'Yes' " clause... it works. It lists the contacts and sorts it by First Name with no problem. But I cant filter it out to only show contacts that have a 'Yes' value on DInteg.

Code on Row Source that gives off a syntax error:

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] WHERE Contacts.[DInteg] = 'Yes' AS Expr1 FROM Contacts ORDER BY Contacts.[First Name];

 

[lcook1974]

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] WHERE Contacts.[DInteg] = 'Yes' [B]AS Expr1[/B] FROM Contacts ORDER BY Contacts.[First Name];

Take the "AS Expr1" out...see if that helps. Since that is in the "WHERE" clause I don't think you need it there.

The other alternative is to create the query in the query builder then point the row source to that query.

if you have a sample DB I'll take a look at it.

Larry

 

[pbaldy]

The clauses are out of order. It should be:

SELECT
FROM
WHERE
ORDER BY

Larry is correct that you also don't want the AS Expr1 in there.

 

[lcook1974]

I didn't even see that....DANG IT!!! I should have caught that too...

Thanks Paul!

Larry

 

[pbaldy]

No problemo, I've done the same thing. Your eyes get locked in on one thing and totally miss the other.

 

[pikoy]

Paul and Larry... you both are awesome!!! Thank you!.

The order totally missed my eye.

Problem Resolved:

Took out AS Expr1 and reordered the expression


For future reference, this is the working code.

SELECT Contacts.ID, Contacts.[First Name] & " " & Contacts.[Last Name] FROM Contacts WHERE 
Contacts.[DInteg] = 'Yes' ORDER BY Contacts.[First Name];

 

[pbaldy]

No problem, and welcome to the site by the way!

 

[pikoy]

Thank you for the Welcome... sure am glad to have found this place.