Subform If Function

[Andy Roberts]

I have a subform on a client form which shows all the invoices specific to a
particular client. On the subform (which is continuous) I have InvNo,
InvDate, InvAmount, Overdue,PaidDate, PayType

The Overdue txt box calculates the amount of days overdue if the InvDate is
more than 30 days ago. If its within 30 days N/D appears in the txt box
(Not Due). This works fine and the code I'm using is

=IIf((Date()-([InvoiceDate]+30))>0,(Date()-([InvoiceDate]+30)),"N/D")

I have used conditional formatting so the background colour of the Overdue
txt box changes depending on whether the account is 30, 60 or 90 days
overdue. This also works fine

2 questions...

1. If I don't enter an Invoice date (because I wanted to input the invoice
but wait until I decide on the date to invoice) I still get N/D in the
overdue box. How Do I keep the Overdue box clear if there is no invoice
date, but still maintain what the about code achieves.

2. The more pressing question is that when I input a paid date I want the
overdue box to grey out but display the amount of days diff between the inv
date and the paid date so I can she how long each invoice took to get paid.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

 

[Mark A. Sam]

Andy,

I'll answer question 1. Try something like this (not tested):

IIf(Isnull([InvoiceDate]),Null,IIf((Date()-([InvoiceDate]+30))>0,(Date()-([InvoiceDate]+30)),"N/D")

I'm not understanding question 2, sorry.

God Bless,

Mark A. Sam

 

[Christopher Robin]

I think you'll need to do this all in a Form OnCurrent event and/or on a
control's After Update event. You can actually do all the conditional
formatting and calculations there, as well as add a check for a null.

If IsNull(Me.InvoiceDate) THEN
With Me.InvoiceDate
..Value = ""
..BackColor =
ELSE

END IF

After a paid date has been entered, you can change the control and disallow
edits.

If Me.PaidDate <> Null THEN
WITH Me.PaidDate
..Enabled = False
..BackColor =
ELSE

END IF

HTH