Re: ACE with PROMPTS and NULLS

Curse me for a novice! As Scott Black and Jonathan Leffler wrote, the where-clause in the ace report can compare a field name to a variable name or the variable name to something else (they suggested NULL but actually in ACE it has to be ""). Proper syntax in Ace to grab records matching variables entered by the user when user enters values at the prompt, or all records when user enters nothing at the prompt: Select ... from ... where ... (a.col1 = $lch_col1 or $lch_col1 = "") Colin M McGrath wrote: > Scott Black wrote: > > > > If I can make some assumptions; > > You would like to search on entered criteria, or return all rows if the > > user simply hits the enter key. > > > > Try this; > > > > Select ... from ... where... > > (a.loca_2 = $lch_loca_2 or $lch_loca_2 is null)> > Did you get that syntax to compile? I was cursing myself for a novice > when I saw your example until I tried it and got: > > -293 IS [NOT] NULL predicate may be used only with simple columns. > > The test for a null value may be applied only to a column-name; not, > for example, to a subscripted character column or an expression. > Review the uses of these keywords in the WHERE clause. -- Colin McGrath cmm@trac3000.ueci.com Raytheon Engineers & Constructors, Inc. (215) 422-4144 Philadelphia, PA, USA Any opinions I state are my own and not necessarily of my employer