InfoPath Dev

I have a field in a database called 'l_rank' which stores the "importance" of the lesson learned on a scale of 1-5

I want the user to have many different search options such as >=3, <=2, 2-4 etc... to show only lessons that have an importance of greater than or equal to 3, less than or equal to 2, in the range 2-4 etc...

How is best to go about this in InfoPath? 

I tried letting the user type in the operators into the query field like the examples given above. Then using JScript i was able to build the correct SQL statement. The problem lies in the fact that InfoPath seems to be doing some automatic checking of the fields, and because the l_rank field is set to the number type, as soon as it sees any sybols such as <>=! then it throws an error. At the moment, when i try to run a query with any of these symbols in the l_rank query field, i get the query field highlighted in red and an error saying

File Cannot be validated by XML schema
Union doesn't support this value
The attribute with 'l_rank' with value '<=5' failed to parse

 Anyone got any ideas on how to solve this problem?

I think there's surely a way to do this, but before that, could you show us the code you're trying to use?

The following is the function that runs when i submit the query. It is based upon an example from an MSDN Tech Note.

function MyQuery::OnClick(eventObj)
{

  // Get the query node(s) that you want to modify.
  var queryProjects = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:projects");
  var queryLessons = XDocument.DOM.selectSingleNode(
  "/dfs:myFields/dfs:queryFields/q:lessons");
  var queryPagination = XDocument.DOM.selectSingleNode("/dfs:myFields");
   
  // Obtain project code
  var strProjCode = queryProjects.selectSingleNode("@proj_code").text;
 
  // Obtain project title
  var strProjTitle = queryProjects.selectSingleNode("@proj_title").text;
 
  // Obtain project type
  var strProjType = queryProjects.selectSingleNode("@proj_type").value;
 
  // Obtain project keywords
  var strProjKeywords = queryProjects.selectSingleNode("@proj_keywords").text;
 
  // Obtain project sector
  // var strProjManager = queryProjects.selectSingleNode("@proj_manager").text;
 
  // Obtain Lesson Category
  var strLessonCat = queryLessons.selectSingleNode("@l_section").value;
 
  // Obtain Lesson Category
  var strLessonRank = queryLessons.selectSingleNode("@l_rank").text;
  XDocument.UI.Alert("Lesson Rank Term:" + strLessonRank);

 
  // Get the default SQL command for the form.
  var strOrigSQLCommand = "shape {select * from [projects] as [projects] ORDER BY [projects].[proj_id] ASC} as [projects] append ({select [l_id],[proj_code],[l_section],[l_actionby],[l_targetdate],[l_problem],[l_solution],[l_rank] from [lessons]} relate [proj_code] TO [proj_code]) as [lessons]";

  var mySQL1 = "";
  var mySQL2 = "";
 
  // Add Project Code to SQL statement if its not blank
  if(strProjCode != "")
  {
    mySQL1 = mySQL1 + "[projects].[proj_code] = \"" + strProjCode + "\"";
  }
 
  // Add Project Title to mySQL statement if its not blank
  if(strProjTitle != "")
  {
    if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
    mySQL1 = mySQL1 + "[projects].[proj_title] LIKE \"%" + strProjTitle + "%\"";
  }
 
  // Add Project Type to mySQL statement if its not blank
  if(strProjType != "")
  {
    if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
    mySQL1 = mySQL1 + "[projects].[proj_type] = \"" + strProjType + "\"";
  }
 
   // Add Project Keywords to mySQL statement if its not blank
  if(strProjKeywords != "")
  {
    if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
    mySQL1 = mySQL1 + "[projects].[proj_keywords] LIKE \"%" + strProjKeywords + "%\"";
  }
 
  // Add Project Manager to mySQL statement if its not blank
  /*if(strProjManager != "")
  {
    if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
    mySQL1 = mySQL1 + "[projects].[proj_manager] LIKE \"%" + strProjManager + "%\"";
    } */

  // Add Lesson Category to mySQL2 statement if its not blank
  if(strLessonCat != "")
  {
    mySQL2 = mySQL2 + "[lessons].[l_section] = \"" + strLessonCat + "\"";
  }
 
  // Add Lesson Category to mySQL2 statement if its not blank
  if(strLessonRank != "")
  {
    // Set up all the Regular Expressions
    var re1 = new RegExp("^[<][2-5]");
    var re2 = new RegExp("^[<][=][1-5]");
    var re3 = new RegExp("^[>][1-4]");
    var re4 = new RegExp("^[>][=][1-5]");
    var re5 = new RegExp("^[1-4]-[2-5]");
    var re6 = new RegExp("^[1-5]{1}");
    var num = "";
    
    if (strLessonRank.match(re1))
    {
        num = strLessonRank.substr(1);
        mySQL2 = mySQL2 + "[lessons].[l_rank] < " + num + " ";
    }
    else if (strLessonRank.match(re2))
    {
        num = strLessonRank.substr(2);
        mySQL2 = mySQL2 + "[lessons].[l_rank] <= " + num + " ";
        XDocument.UI.Alert("Recognised as <=");
    }
    else if (strLessonRank.match(re3))
    {
        num = strLessonRank.substr(1);
        mySQL2 = mySQL2 + "[lessons].[l_rank] > " + num + " ";
    }
    else if (strLessonRank.match(re4))
    {
        num = strLessonRank.substr(2);
        mySQL2 = mySQL2 + "[lessons].[l_rank] >= " + num + " ";
    }

    else if (strLessonRank.match(re5))
    {
        num = strLessonRank.split(",");
        mySQL2 = mySQL2 + '[lessons].[l_rank] BETWEEN ' + num[0] + ' AND ' + num[1] + ' ';
    }
    else if (strLessonRank.match(re6))
    {
        mySQL2 = mySQL2 + "[lessons].[l_rank] = " + strLessonRank + " ";
    }
    else
    {
        XDocument.UI.Alert("Not Recognised Lesson importance Search Term");
    }
 } // End of if(strLessonRank != "")

 
  // Construct the final query string.
  var strSQLQuery = strOrigSQLCommand;
  if( mySQL1 != "")
  {
    strSQLQuery = strSQLQuery.replace("from [projects] as [projects]", "from [projects] as [projects] WHERE " + mySQL1 );
  }  
  if(mySQL2 != "")
  {
    strSQLQuery = strSQLQuery.replace("from [lessons]", "from [lessons] WHERE " + mySQL2 );
  }  
XDocument.UI.Alert (strSQLQuery);
  // This is the query.
  // XDocument.UI.Alert(strSQLQuery);
  // queryLessons.selectSingleNode("@l_problem").text = strSQLQuery;
 
  // Run the query.
  XDocument.QueryAdapter.Command = strSQLQuery;
  XDocument.Query();
 
  // Restore all the user entries to the Query fields so that the user entries will
  // be available if you want to modify and to rerun the query.
  // queryProjects.selectSingleNode("@proj_code").text = strProjCode;
  // queryProjects.selectSingleNode("@proj_title").text = strProjTitle;
  // queryLessons.selectSingleNode("@l_section").text = strLessonCat;
 
  // Restore the default table command (for the next time).
  XDocument.QueryAdapter.Command = strOrigSQLCommand;
 
  // Switch to data entry view to see results.
  //XDocument.View.SwitchView("Data Entry");
}

Looks like there's a potential problem with l_section and l_rank.  You are not concatenating a logical operator between them so you can wind up with something like

from [lessons] where [lessons].[l_section] = "lesson category" [lessons].[l_rank] < 4

I'm not sure if that's the problem here, but the first thing you can do to troubleshoot this issue is display the sql command you're using in a textbox on your form and inspect it for problems.  Feel free to post it here as well.