another dilemma

[Les]

I have fields called [month1] thru [month60]. if i subtract [monthleft] from [months2pay] i get the month number. now i need to pick up the data from the field that corresponds.


' ** TEXT3 has:
="[month"&[months2pay]-[monthleft]&"]"

' ** OUTPUT field
=IIf(Format(DateAdd("m",([months2pay]-[monthleft]),[firstpayment]),"yymmdd")<Format(Now(),"yy") & Format(Now(),"mm") & "15","We Have not Recieved Your Payment For Last Month blah blah","Payment for last Statement in the amount of " & [text3] & " - THANK YOU!")

Right Now I'm getting the text "[month1]" (ie. Payment For the Last Statement in the Amount of [month1] ) instead of the data in [month1].

Is that confusing enough?

Can anyone tell me how to go about this

 

[Rich]

You need to normalise your structure first, you don't need 60 fields to enter monthly payments. Try searching here for Normalise

 

[Les]

More details Rich

thanks,
Les

 

[Rich]

In very simple terms, Table1 has customer detail, amount of loan, startDate, payment frequency, etc
Table2 which has a one to many relationship with 1 and is linked by say CustID has PaymentDate, AmountPaid etc
from this very simplified example you can use queries and or vba to decide who is in arrears, balances etc.
You have used a spreadsheet approach within Access, something which happens quite often.
Search here for posts on Normalising your structure to get more of an idea, post back for more help if need be.

 

[Les]

Thank You Rich. I've played with it and was able to create a normalized test db with one-to-many relationship. Customer data on one side and payment detail on the many.

Now, Let's say a customer will start payment on jan 1, 2003 and he has 6 months to pay it. How do i go about automating the entry of the payment date on the many side?

Many Thanks,
Les

 

[Rich]

You don't, if he/she hasn't paid then the dates will not be there. Do you want to produce a statement for the customer showing when payments are due?

 

[Les]

I'd like to show the dates for the people that update the loan and also make that the base for any late fees that needs to be imposed.