Link to home
Start Free TrialLog in
Avatar of lapucca
lapucca

asked on

I keep getting zero search result with my CAML query

Hi,
I'm using Caml to query a Document Library in SharePoint Online or Office365.  I tried running these 2 CAML in U2U and SPCAML tools and both just returns 0 search result.  I am really not familiar with CAML query syntax.  I would appreciate some help here.  Oh, I also set up 2 document's properties to meeting the Where conditions.

Using today's date, the qualified date would be Leq 11/25/2017 and Geq 1/24/2018.  In 2 document's properties I set them to be 11/30/17 and 12/8/17.   But running these 2 CAML query will locate 0 result when these 2 document's property meets the condition.  Very confused.

Query 1
<Query>
   <Where>
      <And>
         <IsNotNull>
            <FieldRef Name='Renewal_x0020_Date' />
         </IsNotNull>
         <And>
         <Leq>
            <FieldRef Name='Renewal_x0020_Date' />
            <Value Type='Date' IncludeTimeValue='FALSE'>[Today+90Day(s)]</Value>
         </Leq>
         <Geq>
            <FieldRef Name='Renewal_x0020_Date' />
            <Value Type='Date' IncludeTimeValue='FALSE'>[Today+30Day(s)]</Value>
         </Geq>
         </And>
      </And>
   </Where>
</Query>

Open in new window


Query 2
<Query>
   <Where>
      <And>
         <Leq>
            <FieldRef Name='Renewal_x0020_Date' />
            <Value Type='DateTime' IncludeTimeValue='FALSE'>[Today+90Day(s)]</Value>
         </Leq>
         <Geq>
            <FieldRef Name='Renewal_x0020_Date' />
            <Value Type='DateTime' IncludeTimeValue='FALSE'>[Today+30Day(s)]</Value>
         </Geq>
      </And>
   </Where>
   <OrderBy>
     <FieldRef Name="Policy_Number" />
   </OrderBy>
</Query>

Open in new window


Thank you.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lapucca
lapucca

ASKER

Hi, That worked!
The original is generate by SP CAML Query Helper Online.  
I actually wants to make sure the date field is not null or blank before I compare the value otherwise I could get an error in C# trying to compare a null value.  
I don't know how to add on the extra <isnotnull> using either tool after having those 2 condition of And so I just typed in the
      <And>
         <IsNotNull>
            <FieldRef Name='Renewal_x0020_Date' />
         </IsNotNull>

Isn't that And is reqired?
Thank you.
<or>
  <IsNotNull><FieldRef Name='Renewal_x0020_Date' /></IsNotNull>
  <and>
  .. your Leq and Geq conditions here
  </and>
</or>

Open in new window


so it becomes

(
(Renewal_x0020_Date is not null)
or
( 
  (Renewal_x0020_Date>...) and (Renewal_x0020_Date<....) 
)
)

Open in new window

Avatar of lapucca

ASKER

Thank you.