This should be obvious, but I cannot figure it out....

I need to extract an entitycollection based upon a supplied value being BETWEEN two table field values. IE:

SELECT Title, Description from MasterTestCodes where TestDate BETWEEN StartDate and EndDate

Where the 'StartDate' and 'EndDate' are part of the MasterTestCodes table, and TestDate is supplied by the program.

The FieldBetweenPredicate does not seem to handle this situation, since it 'inverts' the between clause to use a single table field and two input bounds.

I am sure there is a simple way to to do this, but I have not found it (yet).

Self-Servicing, 2.0.5027

stoneyowl wrote:

This should be obvious, but I cannot figure it out....

I need to extract an entitycollection based upon a supplied value being BETWEEN two table field values. IE:

SELECT Title, Description from MasterTestCodes where TestDate BETWEEN StartDate and EndDate

Where the 'StartDate' and 'EndDate' are part of the MasterTestCodes table, and TestDate is supplied by the program.

The FieldBetweenPredicate does not seem to handle this situation, since it 'inverts' the between clause to use a single table field and two input bounds.

I am sure there is a simple way to to do this, but I have not found it (yet).

Self-Servicing, 2.0.5027

Try

SELECT Title, Description from MasterTestCodes where TestDate >= StartDate and TestDate <= EndDate

Thanks Rogelio

Indeed, there's no VALUE between FIELD1 and FIELD2, as that's easily rewritten as 2 compare value predicates. The between predicate supports: FIELD between VALUE1 and VALUE2 FIELD between VALUE1 and Field2 FIELD between Field2 and VALUE1 FIELD between Field2 and Field3

as all predicates always focus on a field, not a value.

Well DOH!

Ever ask yourself how you can be so smart when you are so brainless?

Too much forest for the trees, I guess.

Thanks for the nudge in the right direction.

I'm also having trouble with Between using the "new" syntax. I'm using the adapter and would like to use the shorter syntax from the documentation:

' VB.NET filter.Add(new FieldBetweenPredicate( OrderFields.OrderDate, Nothing, dateStart, dateEnd)) ' or: ShippingDate BETWEEN RequiredDate and dateEnd filter.Add(new FieldBetweenPredicate( OrderFields.ShippedDate, Nothing, OrderFields.RequiredDate, dateEnd))

I've tried these with no success

1) filter.Add(OrderFields.OrderDate Between startdate and enddate)
2) filter.Add(New FieldBetweenPredicate(OrderFields.OrderDate between startDate and endDate))
3) filter.Add(New FieldBetweenPredicate(OrderFields.OrderDate, startDate, endDate))
4) filter.Add(New FieldBetweenPredicate(EntityFieldFactory.Create(OrderFieldIndex.OrderDate), startDate, endDate))

This one works but the syntax is less intuitive than what the documentation seems to show.

filter.Add(PredicateFactory.Between(OrderFieldIndex.OrderDate, startDate, endDate))

What is the correct way to do this using the new syntax?

Joel Reinford Data Management Solutions LLC

FieldBetweenPredicate and FieldCompareSetPredicate aren't constructable using operator overloads.

So there is no new syntax available for the Between predicate.

I assume then that I'm just reading the documentation incorrectly. Are you saying that this line from the documentation is actually not .NET code but the representation of SQL code?

' or: ShippingDate BETWEEN RequiredDate and dateEnd

I'm still thinking that #3 and #4 above don't work either but I'll have to check them again.

Joel Reinford Data Management Solutions LLC

jovball wrote:

I assume then that I'm just reading the documentation incorrectly. Are you saying that this line from the documentation is actually not .NET code but the representation of SQL code?

' or: ShippingDate BETWEEN RequiredDate and dateEnd

That's the SQL representation of the line of code below it: ->

// or: ShippingDate BETWEEN RequiredDate and dateEnd
filter.Add(new FieldBetweenPredicate( OrderFields.ShippedDate, null, OrderFields.RequiredDate, dateEnd));

I'm still thinking that #3 and #4 above don't work either but I'll have to check them again.

3 and 4 are equivalents, for selfservicing. If you need adapter code, you have to add a ',null' after the field specification