Difficulty relating a calc field

[caveat1]

I have a table with these 2 fields, both with Number results:

cn__ilnMonth = Month ( cd__ilnDate )

cn__ilnPeriod = If ( cn__ilnMonth < 7 ; 1 ; 2 )

I am getting the correct results in the ct__ilnPeriod field. I am trying to relate this table to another by using this field and relating it to a simple numeric data entry field in the other table. It looks like something is must be wrong with the results of the Period calculation. I don't see any hidden spaces, so I am not sure what I am missing. Any ideas?

Originally, I wanted the results of the calc to be "January-June" and "July-December." I have simplified it to 1 and 2 to figure out the problem, but get the same results. Ideally I would like to relate the tables by th values "January-June" and "July-December," if that is possible.

[Ender]

Are the resulting field types of both keys the same (both number or both text)?

Is the key on the child side of the relationship stored and indexed?

[caveat1]

They are both number type. I tried to store and index the calc field, but cd__ilnDate is a calc field that references a related field so I can't store or index it.

The table with the calc fields is a line items table for invoices. The related table is a royalties statement table, to pay royalties on sales of a particular item, falling within a particular royalty period. The royalty period is either January-June or July-December. There is a separate field for the royalty year.

What I want to do is calculate the royaly period and year for each line item sold, based on dates on the invoice. The cd__ilnDate field references the invoice file with an if statement and gets the invoice date or the paid date, depending on the type of invoice. Then I broke the date out using the Month and Year functions.

Now I want to relate the line items table to the royalty table with these 2 fields and the item number, in order to get the sales of that item that fall into that period. Is this possible? I thought I had the Year portion working in the relationship yesterday, but now can't get it to work either . Any help would be appreciated.