"IF service date in B falls between beginning date and ending date in A."
But what you've presented are not dates. Neither seems logical to call them dates since 444 is year 0002 and 07152011 is invalid date. I have never seen an OS-level regional setting of dates set to MMDDYYYY like that!
Please clarify whether the data type of these 'dates' are numbers or text and why you call them dates. :^)
Anyway, with true dates it would look like this:
Parent::ID = Child::ParentID
Parent::Beginning Date <= Child::ServiceDate
Parent::Ending Date > = Child::ServiceDate
... and this would work if the 'dates' were numbers also but, as numbers, the format is incorrect to produce a true date span.
true, but they represent dates. Let me clarify. The actual variable construct is a eight character (text) field, which is in the format of YYYYMMDD. All three of the variables in question (begin, end and service) are all in this format.
Thank you for looking at this.
Okay. That is not what you provided by example. If in that format and if NUMBER, it will work. Comparative operators in multi-predicate will not work on text relationships.
Since it always starts with a number (year 2011), you can safely change all three fields to number; no worry about dropping a beginning 0. :^)
Thanks, I was about to write the same thing. Again, thank you for helping me clarify. I will give your solution a shot.