filter is not working 1

[alisaif]

Hi,

In a SELECT-SQL I am unable to get my result. I want all the records from AcTran.dbf except those who have the following heads:

"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES"

dt2 = {30/06/2014}

lcString = '"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES"'

SELECT Actran.reference, Actran.cdate, Actran.ccode, Code.description,;
  Actran.debit, Actran.credit, Code.chead;
 FROM ;
     village!actran ;
    INNER JOIN village!code ;
   ON  Actran.ccode = Code.ccode;
 WHERE  (Actran.cdate <= ( dt2 )) AND Code.cHead In(&lcstring) ;
 ORDER BY Actran.ccode INTO CURSOR tActTran READWRITE

Table "Code" contains the following heads:
PARTYC
PARTYS
EMPLOYEE
EXPENSES
LOAN/DEPOSIT
OTHER INCOME
FIXED ASSETS
MISC.
BANK
CASH
STOCK

What I am missing?

Thanks

Saif

 

[EzLogic]

dt2 = {30/06/2014}

select ;
  ACtran.reference,;
  ACTran.cDate,;
  ACtran.cCode,;
  ACtran.Description,;
  ACtran.Debit,;
  ACtran.Credit,;
  ACtran.cHead ;
 from Village!ACTran ;
    Inner Join Village!Code ;
  on ACTran.cCode = Code.cCode ;
    Where ACtran.cDate <= dt2 ;
         and NOT INLIST(Code.cHead,"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES") ;
   Order by ACTran.cCode ;
 Into cursor tACTran READWRITE

Ez Logic
Michigan

 

[alisaif]

Thanks for the reply!

  on ACTran.cCode = Code.cCode ;
    Where ACtran.cDate <= dt2 ;
         and NOT INLIST(Code.cHead,"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES") ;

I want all the records which does not contains the following heads

"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES"

Following does not showing the desired request:

  on ACTran.cCode = Code.cCode ;
    Where ACtran.cDate <= dt2 ;
         and INLIST(Code.cHead,"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES") ;

Thanks

Saif

 

[Mike Lewis]

Saif,

You are missing a "not":

on ACTran.cCode = Code.cCode ;
    Where ACtran.cDate <= dt2 ;
         and [b]NOT[/b] INLIST(Code.cHead,"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES") ..

.

Also, it might be a good idea to Upper and trim the search terms:

on ACTran.cCode = Code.cCode ;
    Where ACtran.cDate <= dt2 ;
         and [b]NOT[/b] INLIST([b]UPPER(ALLTRIM([/b]Code.cHead[b]))[/b],"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES") ..

.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks

What about if I want only the following:

"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES"

Thanks

Saif

 

[Mike Lewis]

What about if I want only the following:

If you do want those, then take out the NOT from my code.

It's like a simple on/off switch.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks mike/Ez Logic I got it. It was really an easy logic which confused me between "not inlist" and "inlist".

Saif

 

[EzLogic]

You mean Ez Logic? lol j/k.

Ez Logic
Michigan

 

[alisaif]

 

[NasibKalsi]

May be it will look easier to read as follows

* To include
on ACTran.cCode = Code.cCode ;
    Where ACtran.cDate <= dt2 ;
         and INLIST(Code.cHead,"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES") = [COLOR=#EF2929]TRUE[/color] ; 

* To Exclude
on ACTran.cCode = Code.cCode ;
    Where ACtran.cDate <= dt2 ;
         and INLIST(Code.cHead,"PARTYC","PARTYS","EMPLOYEE","BANK","EXPENSES") = [COLOR=#EF2929]FALSE[/color] ;

 

[alisaif]

Thanks

Saif

 

[TamarGranor]

Nasib - I strongly disagree with adding "= .T." or "= .F." to a condition. I think that makes the code much LESS readable, especially with .F.

Tamar

 

[Dan Freeman]

I'll agree with Tamar here, particularly where there's a NOT involved.

NOT <expr> = .F.

If <expr> is of any complexity at all it quickly becomes difficult to figure out what's being negated. I like code that's readable at a glance.

 

[NasibKalsi]

Agree with Tamar and Dan. It was to show the difference between the two statemets as Saif expressed in his previous post.