creating a join for between variables value

[mylesg]

Please help a moderate user. I can do basic joins with little problem- but having trouble with this twist. FM Adv 11 on Mac.

Need to join two tables (say A and B ).

Table A varaibles has a ID, Beginning Date, Ending Date (like 444, 07012011, 07302011)

Table B also has an ID, and a service date (like 444, 07152011).

I need to join records IF

ID = ID (not a problem)

AND

IF service date in B falls between beginning date and ending date in A.

It is this second one that gets me. I have only done = joins, never a "falls between" two variables join.

Any help?

[doughemi]

A::ID = B::ID

A::ServiceDate < B::EndingDate

A::ServiceDate > B::StartingDate

Edit: you may want A::ServiceDate ≤ B::EndingDate and A::ServiceDate ≥ B::StartingDate

[LaRetta]

"Beginning Date, Ending Date (like 444, 07012011, 07302011)"

Hi mylesg,

As an aside to Doug's good advice, these are not date fields, right? 444 is date 3/20/0002 and 07302011 is an invalid date (unless it is set that way at the OS level and I've never seen anyone with regional settings as such and in which case 444 is again invalid). The relationship should still work if they are numbers but if they are text they relationship will not work. Can we get clarification on those dates?

[bruceR]

First of all, are the dates real FileMaker date fields? Because you present them as numbers; in a format DDMMYYYY.

If they are actually number fields you will need to format them as YYYYMMDD to do comparisons.