Listbox filtering

[jrjr]

I have done this before with success but can't seem to sort this out for some reason. Maybe someone can spot the trouble...

I am trying to filter a listbox on my form with a combo. Here is the code behind the combo:

Private Sub RepSort_AfterUpdate()

Me.listbox1.RowSource = "SELECT [Basic Entry].[Record Number], [Basic Entry].[Reps], [Basic Entry].[Date], [Basic Entry].[Inquiry Type], [Basic Entry].[Inquiry Sub-category], [Basic Entry].[Policy Type], [Basic Entry].[Caller Type], [Basic Entry].[Irate Call] " & _
"FROM Basic Entry " & _
"Where {{{[Basic Entry].[Reps]=[Forms]![Entry_Form]![RepSort] Or [Forms]![Entry_Form]![RepSort] Is Null)=True))"

listbox1.Requery
End Sub

After changing the combo (RepSort), the listbox is blank. I have tried this with a table as source and as a query as source. Also with and without the requery.

If someone can spot what is wrong with the code, please fill me in!

Thanks

 

[KenHigg]

Why not just set the rowsource to a pre-built query intead of a sql string? I think it would be a slight bit faster and you wouldn't have to wrestle with the sql string syntax...

 

[jrjr]

Ok, I did that. Still get the same result.
Here is the query code:

SELECT [Basic Entry].[Record Number], [Basic Entry].Reps, [Basic Entry].Leads, [Basic Entry].Supervisor, [Basic Entry].Date, [Basic Entry].[Inquiry Type], [Basic Entry].[Inquiry Sub-category], [Basic Entry].[Policy Type], [Basic Entry].[Caller Type], [Basic Entry].[Appropriate Question], [Basic Entry].[Question Asked], [Basic Entry].Answer, [Basic Entry].[Walk Up], [Basic Entry].[Queue Call], [Basic Entry].[Irate Call]
FROM [Basic Entry]
WHERE [Basic Entry].[Reps]=[Forms]![Entry_Form combos]![RepSort] Or [Forms]![Entry_Form combos]![RepSort] Is Null=True;

Then I used:
Me.listbox1.RowSource = "MyQueryName"
behind the combo

 

[KenHigg]

Then did you requery the listbox?

Me.listbox1.requery

???

 

[jrjr]

Yes, sure did

 

[KenHigg]

So what happens, The rows in the list box do not change or what?

 

[jrjr]

All records vanish in the listbox

 

[KenHigg]

I suspect your query criteria is a-muck...

Looks like the use of is null thing as a criteria...

 

[jrjr]

I got it sorted... thanks for helping!! Here is the query that worked:

SELECT [Basic Entry].[Record Number], [Basic Entry].Reps, [Basic Entry].Leads, [Basic Entry].Supervisor, [Basic Entry].Date, [Basic Entry].[Inquiry Type], [Basic Entry].[Inquiry Sub-category], [Basic Entry].[Policy Type], [Basic Entry].[Caller Type], [Basic Entry].[Appropriate Question], [Basic Entry].[Question Asked], [Basic Entry].Answer, [Basic Entry].[Walk Up], [Basic Entry].[Queue Call], [Basic Entry].[Irate Call]
FROM [Basic Entry]
WHERE ((([Reps]=[Forms]![Entry_Form combos]![RepSort] Or [Forms]![Entry_Form combos]![RepSort] Is Null)=True));

 

[KenHigg]

Cool...

FYI - Just for something to do, you should be able to get that string (or a slight variation) to work where you were trying set the rowsource as a sql string...

 

[jrjr]

Ok, and thanks again. I didnt realize that using a pre-built query would improve performance or I would have used that all along. I thought it was the other way around!