Transform vba query in stored query

[georgesOne]

Hi,
I have following query in VBA SQL in a module which works fine:

Set rst = dbs.OpenRecordset("SELECT 'BE ' & tbl_Optics.[Product Name] AS [Product Name], tbl_Optics.[Batch #], tbl_Optics.[Film Thickness], tbl_Optics.Wavelength, tbl_Optics.[Refractive Index], tbl_Optics.Absorption, tbl_Optics.MeasuredBy, tbl_Optics.MeasuredAt, tbl_ProductSub.[Product#], tbl_Products.[DB Family2#], tbl_ProductHierarchy.[PH Sub Class1#]" & _
" FROM (tbl_ProductHierarchy INNER JOIN tbl_Products ON tbl_ProductHierarchy.[PH Sub Class1#] = tbl_Products.[PH Sub Class1#]) INNER JOIN (tbl_ProductSub INNER JOIN tbl_Optics ON tbl_ProductSub.[Product#] = tbl_Optics.[Product#]) ON tbl_Products.[DB Family2#] = tbl_ProductSub.[DB Family2#]" & _
" Where tbl_ProductHierarchy.[PH Sub Class1#] " & IIf([Forms]![frm_ActivePage].[Form]![cbo_SubClass] = "< All >", "< 1000", "= " & [Forms]![frm_ActivePage].[Form]![cbo_SubClass]) & _
" AND tbl_Products.[DB Family2#] " & IIf([Forms]![frm_ActivePage].[Form]![cbo_Family] = "< All >", "< 1000", "= " & [Forms]![frm_ActivePage].[Form]![cbo_Family]) & _
" AND tbl_ProductSub.[Product#] " & IIf([Forms]![frm_ActivePage].[Form]![cbo_Product] = "< All >", "< 1000", "= " & [Forms]![frm_ActivePage].[Form]![cbo_Product]) & _
" ORDER BY 'BE ' & tbl_Optics.[Product Name]", dbOpenDynaset)

I have several such queries in my code and would like to replace them with stored queries. This work well, if I exclude the WHERE clause, but I can not figure out, how to replace the IIF syntax into the query view.
I typically get messages like 'Too few parameters: expected 3'.
Any help would be appreciated.

Thanks, Georges

 

[wtmckown]

That immediate if is valid in the querey editor. You just don't have to work so hard to string it together.....drop the double quotes, the underscores and the ampersands. A good way to zero in on the correct syntax for this type of thing is to use the expression builder in the query builder and look to see how it builds it.

Like this:

Where tbl_ProductHierarchy.[PH Sub Class1#] = IIf([Forms]![frm_ActivePage].[Form]![cbo_SubClass] ='< All >', '< 1000', [Forms]![frm_ActivePage].[Form]![cbo_SubClass]) .............And so forth.