Hello, I was wondering if someone might be able to help me figure out how to search a range of fields.

Here is a simple database table example:

Table: Report

ReportNumber StartMile EndMile

how can I do a range search that would extract each report based off two numbers. I tried a between but it's not really giving me the correct reports.

Say you have these four reports:

ReportNumber StartMile EndMile

001 5 10 002 1 4 003 25 50 004 78 89

If I did a search, (show me all reports from mile start of 4 to mile end of 55

it should pull reports 001, 002 and 003 because one of the intermediate miles of the search request happens to fall inside the range. 4, 5, 6, 7, 8, 9, 10, 11, 12....55, one of those numbers fall within report 001, 002 and 003.

would the where clause look something like this?

Where StartMile <= 4and EndMile >= 55 Or StartMile >= 4 And StartMile <= 55 Or EndMile>= 4 And EndMile<= 55

or am I totally missing something here?

Thanks!

As I see, either the StartMile or the EndMile should be in range, so this should be enough:

Where
     StartMile >= 4 And StartMile <= 55
     Or EndMile>= 4 And EndMile<= 55

And this is how the code would look like:

int startMileParam = 4;
int endMileParam = 55;

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(ReportFields.StartMile, startMileParam, endMileParam));
filter.AddWithOr(new FieldBetweenPredicate(ReportFields.EndMile, startMileParam, endMileParam));

daelmo wrote:

As I see, either the StartMile or the EndMile should be in range, so this should be enough:

Where
     StartMile >= 4 And StartMile <= 55
     Or EndMile>= 4 And EndMile<= 55

And this is how the code would look like:

int startMileParam = 4;
int endMileParam = 55;

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(ReportFields.StartMile, startMileParam, endMileParam));
filter.AddWithOr(new FieldBetweenPredicate(ReportFields.EndMile, startMileParam, endMileParam));

Thanks! that does it