WHERE clause

[skate]

Can someone give me some assistance? I'm not sure how to use the where clause. I have a listbox in which i want to use to display a filtered list of records showing only items checked yes as a "hot list" item. When I hit the toggle button it should display this. Instead, it rearranges my list of items in sort strange order and still displays all the records.

Private Sub ToggleHotList_Click()
Dim txtSearchString As Variant
Dim strSQL As String
'strSQL = "SELECT [Files].[File Index], Files.[Hot List], [Customer].[Customer ID], [Files].[Project], [Files].[Eng], [Files].[Proposal], [Files].[Mill Company], [Files].[Mill Location], [Files].[Description], Files.[Detail Desc] FROM Files INNER JOIN Customer ON [Files].[Customer Index]=[Customer].[Customer Index]"
strSQL = strSQL & "WHERE ((Files.[Hot List]) = Yes) "
strSQL = strSQL & "ORDER BY Files.[Hot List]"
Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtSearch.SetFocus
End Sub

 

[jiqjaq]

When you construct SQL in code it's somewhat different than doing it in the Query wizard's design view. The "Hot List" field is not actually assigned YES or NO. Like all all boolean values, they are assigned -1 for True (yes) and 0 for False (No).

Try strSQL = strSQL & "WHERE (Files.[Hot List] = -1)"

Since you're only asking for those on the Hot List and you're sorting on the same field, you haven't really sorted anything. All the other fields will probably be assigned in the order in which they appear on their original tables, but even that's not guaranteed. You need to explicilty state something like "ORDER BY [Files].[Project], [Customer].[Customer ID], ...".

Also, use underscores "_" instead of spaces in the field and table names so you can avoid keying in all those brackets. The code is much easier to look at without them. The Access wizard automatically puts them in so it won't have check for spaces. You shouldn't have to check either.

 

[JeremyNYC]

Well, you can certainly do
"WHERE ((Files.[Hot List]) = Yes)" in code if you can do it in the QBE grid, I promise that much.

You don't have a space before the word "WHERE" or after the part that comes before this, so I'm surprised it even runs.

It doesn't make sense that you'd be sorting by the same boolean field on which your filtering--everything returned will have the same value in that field.

When you say its sorted in a "strange order" that makes me think you might be expecting it to be sorted in some particular order. Unless you give it meaningful sort instructions it will be sorted in whatever way it's grabbed, not in any particular order.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.