Why is it everything in the table is shown? 3

[Mandy_crw]

Hi everyone... please help me filter the cursor....

REPORT FORM LoadWallet FOR Details.transcode = "LOD" OR transcode = "CSH" AND BETWEEN(ttod((details.deyt)),start,ind) PREVIEW WINDOW (oRepForm.Name) ;
TO PRINTER PROMPT

Why is it everything in the table is shown even if I specify the dates? but when i change it to

REPORT FORM LoadWallet FOR Details.transcode = "LOD" AND BETWEEN(ttod((details.deyt)),start,ind) PREVIEW WINDOW (oRepForm.Name) ;
TO PRINTER PROMPT
it works well? Can it not work with two criteria? if so, please help me how to have at least two criteria and should also include 'BETWEE'. Thanks....

 

[TamarGranor]

The problem is the way AND and OR work. Try this:

[pre]REPORT FORM LoadWallet FOR (Details.transcode = "LOD" OR transcode = "CSH")AND BETWEEN(ttod((details.deyt)),start,ind) PREVIEW WINDOW (oRepForm.Name) ;
TO PRINTER PROMPT
[/pre]

By rule, AND is executed before OR (just as multiplication happens before addition), so your condition was being understood as:

[pre]Details.transcode = "LOD" OR (transcode = "CSH" AND BETWEEN(ttod((details.deyt)),start,ind))[/pre]

When I was in about 8th grade, my teacher taught me a mnemonic that went "Please Excuse My Dear Aunt Sally" and stood for the other of operations in arithmetic:

- Parentheses;
- Exponentiation;
- Multiplication and Division;
- Addition and Subtraction.

In logic, AND is considered multiplication, while OR is considered addition.

Tamar

Tamar

 

[Chris Miller]

Mandy,

it's true and good to know what Tamar points out. Now the question is what you want to print.

My guess is you get there by putting a paranthesis around the conditions you OR, because of the precedenec of AND before OR:

FOR (Details.transcode = "LOD" OR transcode = "CSH") AND BETWEEN(ttod((details.deyt)),start,ind)

And in some way more failsafe you could use

FOR INLIST(Details.transcode,"LOD","CSH") AND BETWEEN(ttod((details.deyt)),start,ind)

Where you can easily extend the list of codes.

Chriss

 

[Mike Lewis]

I make it a rule always to use parentheses when I have multiple ANDs, ORs and NOTs, even where they are not required. That way I don't have to think about it. The same is true for the arithmetic operatores (for adding, multiplying, etc.)

Tamar, I didn't know the Aunt Sally mnemonic. The one I learned was BODMAS:

- brackets
- order (= exponentiatoin)
- division
- multiplication
- addition
- subtraction

And I have vague memory that someone once set that to music.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mandy_crw]

Thank you so much everyone for all your answers, i also had an answer which gave me the output i want... will there be problem if in case...Thanks

SELECT * FROM trans WHERE transcode = "LOD" OR transcode = "CSH" AND BETWEEN(ttod((deyt)),start,ind) ORDER BY particular INTO CURSOR DETAILS

SET FILTER TO BETWEEN(ttod((deyt)),start,ind)

REPORT FORM Transaction PREVIEW WINDOW ("Transaction") TO PRINTER PROMPT

SET FILTER TO

 

[Mandy_crw]

Thank you Tamar, Chriss, & Mike.... It's only now that i have known that there is also the order of operation.... God bless....

 

[Chris Miller]

Mandy,


while it may give you the desired range of records to set a filter after you query with the unmended WHERE clause, it's a far better solution to mend the where condition so you get this result without adding in a filter as aftermath.

Understand what you're conditoin is asking for is all data for LOD but only a specific time range for CSH, because the AND condition only is applied to all CSH data, the OR separates LOD from that, ALL of LOD data. Maybe you didn't realize that CWSH data already was limited to the time range.

The aftermath filter says you want that same timerange for all data, also for LOD. And that's what you can get in one go, too, just put it right. Don't stick to this hack of a solution.

Lets look at what you do by making it a two step process. You first apply the filter

transcode = "LOD" OR transcode = "CSH" AND BETWEEN(ttod((deyt)),start,ind) && call this condition1

Then you apply a second filer as aftermath:

BETWEEN(ttod((deyt)),start,ind)  && call this condition2

Putting that together meanns the overall filter is:

(condition1) AND (condition2)

And intentionally I put in the paranthesis, though using the names condition1 and condition2 for the whole conditions doesn't need parantheses. But if you substitute in the actual expressions, you need them. Substituting in the terms you get:

(transcode = "LOD" OR transcode = "CSH" AND BETWEEN(ttod((deyt)),start,ind)) AND (BETWEEN(ttod((deyt)),start,ind))

That doesn't only look a bit redundant, it can be simplified to

(transcode = "LOD" OR transcode = "CSH") AND (BETWEEN(ttod((deyt)),start,ind))

I did a few steps here in one step, let's do that slower: The outer AND condition is one that would be applied to all ORed conditions. Just like you can expand (2+3)*4 to become (2*4+3*4):

transcode = "LOD" AND (BETWEEN(ttod((deyt)),start,ind)) OR transcode = "CSH" AND BETWEEN(ttod((deyt)),start,ind) AND (BETWEEN(ttod((deyt)),start,ind))

now the last AND condition2 is redundant.

transcode = "LOD" AND (BETWEEN(ttod((deyt)),start,ind)) OR transcode = "CSH" AND (BETWEEN(ttod((deyt)),start,ind))

And then you can see the same AND condition can be factored outside again:

(transcode = "LOD" OR transcode = "CSH") AND (BETWEEN(ttod((deyt)),start,ind))

Notice, that introduces the finally remaining paranthesis around the OR condition.

Finally, since the BETWEEN condition is only one condition, the paranetheses around that can be removed:

(transcode = "LOD" OR transcode = "CSH") AND BETWEEN(ttod((deyt)),start,ind)

So you end up with one of the overall conditions I suggested. Shortening the OR condition to an INLIST condition makes it simpler to add a new code.

It means your two step filter can be changed to what I suggested.

Chriss

 

[TamarGranor]

I've heard BODMAS before. Seems this is a case of "two peoples separated by a common language."

Tamar

 

[Mandy_crw]

That was so comprehensive Chriss... I've used what you have given Chriss.... Thank you so much.... God bless

 

[Mike Lewis]

It seems that I was right about there being a BODMAS song. There are dozens of performances of it on the likes of YouTube and Spottify. I particularly like this one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

They could almost do this song based on the Batman intro.

Nananananananannananananananan, Bodmas!

Chriss