Where Clause Question

[fredka]

I have a form where I want to populate a list box (named region) from a table (named opportunities)

I have not problem doing this by adding the following to the on open event of the form:

Me.Region.RowSource = "SELECT DISTINCT [Opportunity Region] FROM Opportunities;"

However, I would like to filter out a few of the selections. I tried adding this:

Me.Region.RowSource = "SELECT DISTINCT [Opportunity Region] FROM Opportunities where [opportunity region] not in ('NJ - HHIA', 'Other than NJ HHIA');"

However, I get no results at all - I have tried several different variations with no luck - any help would be greatly appreciated!! thanks!!!

Fred

 

[PHV]

I'd try to use the Load event instead of the Open.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

fredka,
Did you try create a query with that exact SQL to see if it works?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredka]

I did try that - I used:

Me.Region.RowSource = "SELECT Opportunities.[Opportunity Region] FROM Opportunities" & _
"GROUP BY Opportunities.[Opportunity Region] HAVING (((Opportunities.[Opportunity Region]) " & _
"<> 'NJ - HHIA' And (Opportunities.[Opportunity Region]) <> 'Other than NJ - HHIA'))" & _
"ORDER BY Opportunities.[Opportunity Region];"

However, the field remains blank. Thanks!!!

 

[fredka]

I figured it out.... It was in the spacing - because I was using the "& _" to add lines together... I needed a space prior to "Group" and "Order"

Thanks!!!!

Fred