I think this is what you are asking for:
Create a new table occurrence (use the button with two overlapping green plus signs) and set up your relationship as
Table1::datestart < newtableTO::date AND Table1::dateened > newtableTO::date AND Table1::AccountNumber = newtableTO::AccountNumber.
I'm suggesting the new TO because that may be your problem. If you have one relationship between the two tables based on account # only, then you need a new table occurrence for the date range relationship.
So the description of the relationship you've made is the same as I've tried to do with no success.
I've created brand new TOs for this layout that I'm working on, so there is no other previous functionality that can be damaged by changing the relationship criteria, if that's at all what you're inferring might be the case.
Simply put, the above described relationship is not working for me. When I add date into the picture it ceases to function.
Then it's time to check more pesky but vital details to see why it doesn't work:
Are the date fields on both sides of the relationship of type "date"? If one is a text field, the relationship won't work.
is the non-global date field a stored, indexed field? You can check this by looking at it's field definition.
If all that seems to be correct, try posting a screen shot of your relationship graph and the relationship options dialog, maybe there's something there. (Screen shots have to be posted to a different site and then you put a link to it here in your post.)
If all else fails, I'll be glad to take a look at your file and see if I can spot the problem.
Check your private messages for a message from me with an email address you can send a clone of your file to.
Bob Schwenkler wrote:
Match fields on the other side are calculation fields that copy over the date from its parent record.
You cannot use an unstored calculation field as the matchfield on the child side of a relationship. Your overall structure is not entirely clear to me (which table has the account number, and which the date?), but perhaps you could use the parent table in the relationship chain.
Ok, unstored calculation field was the problem. I thought it might be some such thing. Thanks for the help!