Query Problems

[Jack Reddan]

I've just started using A5V4 on my major database which had been
running on A4. Everything seems OK but I cannot get relatively
simple queries to work.
I want to select any record the "is not blank" in a variety of
fields -- these are all OR's any of these fields being non-blank
should select the record.
1) If I use the query genie, it gives me an OR, but it uses the
same field for both the first and second condition, thus the 2nd
condition is defective.
2) If I use the query card it makes both conditions AND even
though I clearly put the 1st in the condition column and the 2nd
in the OR column.

I've tried all sorts of combinations, none work.

I installed from the Version 4 CD and downloaded the Patch403.exe
file on 6/12/00 and installed it. Tried installing it again.
No change.

This seems like an obvious old problem that should have been fixed
by the 403 update.
Please help!

[Bill Warner]

RE: Query Problems

Here's a simple solution using Query by Form:

Open QBF, go to the first field and type IS NOT BLANK. Now click on the NEXT button in the tool bar (to go to the next page of the query, which is the same as an OR function), and type IS NOT BLANK in the next field.

I think you got confused in the query card, because the OR column is meant for an OR condition for the same field. In order to get it to work as you want, you would have to put the entire expression, including the new field, in the OR column.

Here's what the script would look like:

tbl = table.current()
query.description = ""
query.order = ""
query.filter = "(ISNOTBLANK("NAME").OR.ISNOTBLANK("CITY"))"
query.options = "I"
tbl.query_create()

[Jack Reddan]

RE: Query Problems

Thanks for the QBF suggestion, that let me cobble something
together. Problem is I have 17 fields that I want to OR for
IS NOT BLANK.

The Query Card looked promising --
The User's Guide on page 32 clearly shows examples of OR with
different fields, each row is used for a separate field and the
condition of each row is placed in a separate column.
It doesn't work the way the book says.

The Genie doesn't work right either -- it allows selection of
different fields for OR conditions, but it turns them all into
the same field.

[JohnZaleski]

RE: Query Problems

You are missing the point made by Bill Warner. In a single row you can reference a different field than that in the original field-expression field (far left ). As Bill said:

"In order to get it to work as you want, you would have to put the ENTIRE EXPRESSION,INCLUDING THE NEW FIELD, in the OR column." Your response to Bill indicates that you haven't gotten that yet.

The queries work, you just need to understand what Bill said. Suggestion: Get a query working with one "OR" only and then work up to what you want.

[Tom Cone Jr]

RE: Query Problems

Also, check the discussion of using multiple forms to create OR conditions using multiple fields... its on pg 38 of the vers. 4 User's Guide.


-- tom

[Bill Warner]

RE: Query Problems

Jack, you might run into a problem with 17 fields. I have done OR expressions and found that they don't work with over 12 or so statements.

Here's a thought: Create a new field in the table called NOT_BLANK, make it character 25 or so long. Then, in field rules, make it a calc field whose expression is

LEFT(FIELD1,1)+LEFT(FIELD2,1)+...

This expression will give you the first character from each field. This way, if any of the fields has any data in it, the NOT_BLANK field will, too. Then you could do your query on this field.

If some of your fields are date or numeric, you would have to make the calc field accept them:

LEFT(LTRIM(STR(NUM_FIELD)),1) or
LEFT(DTOC(DATE_FIELD),1)

I tried this with a few fields, and it worked fine. I don't know if you'll run into any problems with 17 fields. If so, you could always add a couple calc fields, and then query on each of these.