Query filter trouble

[Ronald Anusiewicz]

The attached jpg file is a screen shot of some code.

What I'm doing is running an xdialog box to filter a report. The xdialog contains 2 check box objects. I referenced the help section on "Using Xbasic to Create Queries".

I open a table and create a query.
Next the filter variable follows the example in the "Using Xbasic to Create Queries" and creates the results of the filter variable in the nrecs dialog box. Cc = "cash" .or. Cc = "Pilot CC".

I then try the following:
append.t_filter = "Transno = var-"vflightno_transno .and. var-"filter"
and I get the following error: "filter expression not logical".

If I hard code it like this:

append.t_filter = "Transno = var-"vflightno_transno .and. var-"filter.and. (Cc = 'Cash' .or. Cc = 'Pilot CC')"

the filter works. But I can't hard code the filter as the check box values will always be changing.

Ron

[Ronald Anusiewicz]

RE: Query filter trouble

Sorry,

I messed up the code that works. It should be:

append.t_filter = "Transno = var-"vflightno_transno .and. (Cc = 'Cash' .or. Cc = 'Pilot CC')"

[Tom Cone Jr]

RE: Query filter trouble

Ron,

Could your script specify alternative filter expressions depending on which checkbox was active?

If cc = "Cash" then
append.t_filter = "Transno = var->vflightno_transno .and. cc = 'Cash'"
else
append.t_filter = "Transno = var->vflightno_transno .and. cc = 'Pilot CC'"
end if

-- tom

[Ronald Anusiewicz]

RE: Query filter trouble

At 2:00 am I figured it out. I never do searches on the Alpha 5V4 board, but I did this morning. There I found a fantastic post from Jerry Brightbill that will alter the way I work with filter expressions for now on.

The code I'm now using is:

append.t_filter = "Transno = "+chr(34)+vflightno_transno+chr(34)+" .and. ("+filter+")"

And a very sincere thank you is going out to Jerry for his help.

I don't know how to link to his post so I've copied and am pasting it here. This is a must read!

Ron

Jerry's Post *****************************************

A filter can easily reference a variable, although the syntaxt isn't immediately clear. The big problem is to establish when the variable is defined or evaluated. The next issue is the use of quotes and double quotes. Since I never seem to keep the usage of single and double quotes
straight in my mind, I use the ascii value for a quote (chr(34) which
always works.

One thing I like to do is to define the filter expression and then send the result to the trace window to see if the result is what I expect. Then and only then, do I complete the script and try to run the query. Saves many headaches.

Examples using your code;

filter_expression = "left(LASTNAME, 1) = '" + mulstbtn1.text + "'"

If the value for mulstbtn1.text is "sha", then you could write the expression as

filter_expression = "left(LASTNAME, 3) = 'sha'" or as

filter_expression = "left(LASTNAME, 1) = "+chr(34)+"sha"+chr(34)

Send the result to the trace window using trace.writeln(filter_expression)

In both cases, the result will be

left(LASTNAME, 3) = "sha"

Now, the get the same result using a variable, I first establish the value of the variable before I define the filter.

dim var1 as c
var1 = mulstbtn1.text

if mulstbtn1.text is "sha", now var1 is equal to "sha". Using the format with the ascii quotes, the resulting expression can be written as

filter_expression = "left(LASTNAME, 1) = "+chr(34)+ var1 +chr(34)

The result in the trace window will show that the variable value is treated as a constant

left(LASTNAME, 3) = "sha"

Some people will use a different method to use a variable in a filter.

filter_expression = "left(LASTNAME, 1) = " + var-"var1

While this can work, there are a couple problems. First, the expression is evaluated when the query runs. Therefore, the query has to have access to the variable value, which means the variable must be defined as shared or global, depending on context. Normally, the variable must be global.

The next problem is that the value for the variable must be
evaluated each time the filter is checked, which is for every record. This can really slow down the query. The first method considered the variable value as a constant, so it is not checked.

Jerry