Tom,

Umm... It's too long because there are too many clauses? < grin >. I'm sure there is a limit for the SQL code for views. I couldn't find it in a quick check of my manuals, but I'm sure there is one.

Are you using this for a Query-by-Example form? Where the user fills in whichever blanks he chooses and the program shows him the records that match? If that's the case, I'd suggest an easier way to handle this:

After the user fills in the form, then run through the fields to develop a SQL WHERE statement.

1. Any empty fields can be skipped.
2. Link each item to the next with 'AND' into a single text variable which we'll call 'cFilter'
3. Don't forget to remove the leading (or trailing, depending on your technique) 'AND'.
4. When you have this long statement pieced together, your SQL would be

SELECT Buildings.* ;
  FROM cpi!agents INNER JOIN cpi!buildings ;
  ON Agents.contactid = Buildings.lpmcoid;
  WHERE &cFilter ;
  INTO cursor MyCursor

Note the Macro (&) in front of cFilter.

With this method, ONLY the clauses desired by the user will be included in the cFilter string.

You can just pull in all the building information, unless your Buildings table has many more fields than those mentioned.

One thing to remember is how the macro interprets data. For example, in your first WHERE parameter you have "LIKE uppper(?bname)" Assuming the user enters a value into a variable 'bname', your code would look like this:

cFilter = cFilter + "AND LIKE '"+upper(bname)+"'"

Note the single quotes which surround the VALUE of upper(bname). This is because the entire cFilter will be a single text string. The actual variables are not used when the SQL is run.

If your cFilter starts to get 'too long' error messages, break it into parts. I've done that for the same reason.

HTH

Barbara

>Here it is. No one has yet to tell me why it's too long. It was created in the View Designer. Also, do you know how to execute a SELECT statement and display the results in a grid? I've tried doing the SELECT INTO a CURSOR, but the grid doesn't show - not even the headers. If I can't use the View Designer, I'll have to simulate what I need to do in code.
>
>SELECT DISTINCT Buildings.name, Buildings.address,;
>Buildings.postalcode, Buildings.built, Buildings.floors,;
>Buildings.location, Buildings.class, Buildings.type, Buildings.totsqfeet,;
>Buildings.totavail, Buildings.larconspac, Buildings.rental1,;
>Buildings.rental2, Buildings.consttype, Buildings.comments,;
>Buildings.city, Buildings.state, Buildings.lpmcoid, Buildings.buildcoid,;
>Buildings.loaddoors, Buildings.railserv, Buildings.sprinkler,;
>Buildings.clearhgt, Buildings.janitorial, Buildings.utilities,;
>Buildings.webaddress;
>FROM cpi!agents INNER JOIN cpi!buildings ;
>ON Agents.contactid = Buildings.lpmcoid;
>WHERE UPPER(Buildings.name) LIKE UPPER(?bname);
>AND UPPER(Buildings.address) LIKE UPPER(?baddress);
>AND UPPER(Buildings.city) LIKE UPPER(?bcity);
>AND UPPER(Buildings.state) LIKE UPPER(?bstate);
>AND UPPER(Buildings.postalcode) LIKE UPPER(?bpostalcode);
>AND UPPER(Buildings.built) LIKE UPPER(?bbuilt);
>AND UPPER(Buildings.lpmcoid) LIKE UPPER(?blpmcoid);
>AND Buildings.floors = ?bfloors;
>AND UPPER(Buildings.location) LIKE UPPER(?blocation);
>AND UPPER(Buildings.class) LIKE UPPER(?bclass);
>AND UPPER(Buildings.type) LIKE UPPER(?btype);
>AND Buildings.totsqfeet = ?btotsqfeet;
>AND Buildings.totavail = ?btotavail;
>AND Buildings.larconspac = ?blarconspac;
>AND UPPER(Buildings.rental1) LIKE UPPER(?brental1);
>AND UPPER(Buildings.rental2) LIKE UPPER(?brental2);
>AND UPPER(Buildings.consttype) LIKE UPPER(?bconsttype);
>AND UPPER(Buildings.comments) LIKE UPPER(?bcomments);
>AND UPPER(Buildings.buildcoid) LIKE UPPER(?bbuildcoid);
>AND UPPER(Buildings.loaddoors) LIKE UPPER(?bloaddoors);
>AND Buildings.railserv = ?brailserv;
>AND Buildings.sprinkler = ?bsprinkler;
>AND UPPER(Buildings.clearhgt) LIKE UPPER(?bclearhgt);
>AND Buildings.janitorial = ?bjanitorial;
>AND Buildings.utilities = ?butilities;
>ORDER BY Buildings.name
>
>Let me know your ideas on this one.
>
>Thanks.