Excluding field values in script

[Anonymous]

Hi All

I have a table which contains Status,Operational Date,Cancellation Date.In Status column some sites are operational and some are disconnected,also some sites which have cancellation date in current month are also shown Disconnected.Now I am excluding Disconnected sites in script itself by giving where Status<>'Disconnected',but,I need values from sites whose status is disconnected but cancellation date is of current month.Please help me in solving this issue.Thank you in advance

[Anonymous]

Hi Tresesco

Now that I understood how to write Expression,but,it is not giving values of sites which are disconnected in November and December when I am deriving values form that fields.Please help.how can I modify above expression so that I can get values for Nov and Dec fields.I think i need to use ADDYEARS function but not sure how to do

[Anonymous]

is it something like

Where  Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

  not (Status='Disconnected' and (month(AddMonths(today(),-1))<>Month([Cancellation Date])) and (Year(AddYears(Today(),-1))<>Year([Cancellation Date])))

[#4]

Hey Vani,

where not isnull(Country) and  Country<>'xyz' and len(trim([Account Number]) > 0)  and not (match(Status,'Disconnected') and (month(AddMonths(today(),-1))<>Month([Cancellation Date]))

and (Year(AddYears(Today(),-1))<>Year([Cancellation Date]))))

[Anonymous · Accepted solution]

I have tried this expression and it is working fine.Thank you all for your help.

           Where  Country<>'' and Country<>'xyz' and len(trim([Account Number]) > 0) and

(Status='Operational' or (Status='Disconnected'  and

([Cancellation Date]>=MonthStart(Today(),-3) and [Cancellation Date]<=MonthEnd(Today(),0) and

([Cancellation Date]>=YearStart(Today(),-1) and [Cancellation Date]<=YearEnd(Today(),0))))) ;