Not to show certain month fields

[Les]

Can anyone please help me with my current dillema.
I'm creating a billing form and i need to breakdown the monthly payments (date). I have a field where users enter the first payment [firstpayment] and another field where they enter the number of months [MonthsToPay]. Now, I breakdown the months by:
Select DateAdd("m",0,[firstpayment]) AS Payd1,
DateAdd("m",1,[firstpayment]) AS Payd2,
DateAdd("m",2,[firstpayment]) AS Payd3,
DateAdd("m",3,[firstpayment]) AS Payd4, 'etc...

Now what i need to do is to only show the dates for the number of months to pay they entered [MonthsToPay].

Please please please help!

Les

 

[Jon K]

I hope I understand your question fully.

For example, if FirstPayment is 11/15/2002 and MonthsToPay is 3, and you want to display:-
11/15/2002 as Payd1, 12/15/2002 as Payd2, 1/15/2003 as Payd3, and Null as Payd4,5,6...etc, then try this:-

SELECT MonthsToPay, FirstPayment as Payd1,
IIF(DateAdd("m",1,FirstPayment)<DateAdd("m", MonthsToPay, FirstPayment) , DateAdd("m",1,FirstPayment), null) as Payd2,
IIF(DateAdd("m",2,FirstPayment)<DateAdd("m", MonthsToPay, FirstPayment) , DateAdd("m",2,FirstPayment), null) as Payd3,
IIF(DateAdd("m",3,FirstPayment)<DateAdd("m", MonthsToPay, FirstPayment) , DateAdd("m",3,FirstPayment), null) as Payd4,
IIF(DateAdd("m",4,FirstPayment)<DateAdd("m", MonthsToPay, FirstPayment) , DateAdd("m",4,FirstPayment), null) as Payd5