Difficulty relating a calc field

January 16, 200718 yr

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.

January 17, 200718 yr

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?

January 17, 200718 yr

Author

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.