SQL Help

[ssatech]

I have a form based on this record source:

Const strSQL = "SELECT [Invlist].[PartID], [Invlist].Stock, [Invlist].[Vendor1], [Invlist].[Vendor2], [Invlist].ReOrder, [Invlist].Type AS PartType, [Invlist].Stocked, [Invlist].Units, [Invlist].Description, [Invlist].Cost, [Invlist].LowStock:IIf([Stock]<=[ReOrder],'Low',"" FROM [Invlist]ORDER BY [Invlist].[PartID]"

I have several filter options buttons


Select Case Me!optFilterBy
Case 1
strFilterSQL = "SELECT [Invlist].[PartID], [Invlist].Stock, [Invlist].[Vendor1], [Invlist].[Vendor2], [Invlist].ReOrder, [Invlist].Type, [Invlist].Stocked, [Invlist].Units, [Invlist].Description, [Invlist].Cost FROM [Invlist] WHERE ((([Invlist].Stock) Is Not Null))"

Case 2
strFilterSQL = "SELECT [Invlist].[PartID], [Invlist].Stock, [Invlist].[Vendor1], [Invlist].[Vendor2], [Invlist].ReOrder, [Invlist].Type, [Invlist].Stocked, [Invlist].Units, [Invlist].Description, [Invlist].Cost FROM [Invlist] WHERE [Cat] = 'BOM';"

The first (2) cases work fine, buts its third case I am having difficulty:
I am having problem writing SQL query to select for “Low” in the recordsource.
I also have an unbound textbox in the form , its recordsource calculates/expresses “Low” in the recordsource query; =IIf([Stock]<=[ReOrder],'Low',"")

Case 3
strFilterSQL = "SELECT [Invlist].[PartID], [Invlist].Stock, [Invlist].[Vendor1], [Invlist].[Vendor2], [Invlist].ReOrder, [Invlist].Type, [Invlist].Stocked, [Invlist].Units, [Invlist].Description, [Invlist].Cost FROM [Invlist] WHERE ((([Invlist].ReOrder) = 'Low';'"

Any help appreciated

 

[Golom]

Comparing your first (Const strSQL) and Case 3 queries, you appear to be treating the field Reorder as a numeric in the first one

(i.e. IIf([Stock]<=[ReOrder],'Low',"" ) )

and as a text string in the second

(i.e. WHERE [Invlist].[Reorder] = 'Low' )

Do you perhaps want

WHERE Invlist.Stock < Invlist.ReOrder

in Case 3?

 

[ssatech]

Golom,

"and as a text string in the second

(i.e. WHERE [Invlist].[Reorder] = 'Low' )

Do you perhaps want

WHERE Invlist.Stock < Invlist.ReOrder"

Thanks a million! That did the trick....

Case 3
strFilterSQL = "SELECT [Invlist].[PartID], [Invlist].Stock, [Invlist].[Vendor1], [Invlist].[Vendor2], [Invlist].ReOrder, [Invlist].Type, [Invlist].Stocked, [Invlist].Units, [Invlist].Description, [Invlist].Cost FROM [Invlist] WHERE [Invlist].Stock < [Invlist].Low