Pulling hair out.....

[claysea]

I want to have an expression that says...

If [Invoice Date] IS NOT NULL and [Date Paid] IS NULL, then return the name of the month (i.e., March) from [Date of Service] field.

Also, it's possible that invoiced for 5 different months and not one of them has been paid on, therefore the formula above would need to return all 5 month's names...not just one. (i.e., March, April, May, June, July)

Does that make sense?

[ItsMe]

Where are you placing your expression?

An IF THEN statement behind a form would look like this

If IsNull(Me.Txt1) AND IsNull(Me.Txt2) then
Exit Sub
end IF

[claysea]

Where are you placing your expression?

An IF THEN statement behind a form would look like this

If IsNull(Me.Txt1) AND IsNull(Me.Txt2) then
Exit Sub
end IF

This is what I have...and I know it's crap, but I don't know else how to write it. :-(


=IIf(IsNotNull([Invoice Date:] & IsNull([Date Paid:])), =DatePart("mm", [Date of Service:]), " ")

[ItsMe]

I don't know either. Where are you placing this?

At a glance.....

=IIf(IsNotNull

may need to be

=IIf(Not IsNull

[claysea]

I am putting it in a Report that pulls information from a table.

[Bob Fitz]

This is untested, but try:
Iif Not IsNull([Invoice Date]) AND IsNull([Date Paid]),Month([Date of Service],"")

If [Invoice Date] IS NOT NULL and [Date Paid] IS NULL, then this should return the number value of the month from [Date of Service] field.

[ItsMe]

Thank you Mr. Fitz

[claysea]

Mr. Fitz,

I get the following error message when I paste your formula in....any ideas?

"You must enclose IIF function arguements in parentheses"

[Bob Fitz]

Sorry, how about:
Iif(Not IsNull([Invoice Date]) AND IsNull([Date Paid]),Month([Date of Service]),"")

[claysea]

Sorry, how about:
Iif(Not IsNull([Invoice Date]) AND IsNull([Date Paid]),Month([Date of Service]),"")

Sorry...It doesn't produce anything; that field is still blank.

[claysea]

I got a little further with this one:

=IIf(Not IsNull([Balance Due:]),([Date of Service:]),"")

The problem is...that it returns the very first date of service (ever) for that customer; instead of just the months he owes a balance.

[ItsMe]

Well it sounds like the syntax is squared away for the most part.

I am still curious where you are putting this. I understand now it is in a report. Is it in an unbound textbox? Is it in a footer to a detail?

[ssanfu]

I would have this in the query for the report, not in a control on the report. Something like

UnPaidMonth:=IIf(Not IsNull([Balance Due:]),Format(([Date of Service:]),"mm"),"")

If [Invoice Date] IS NOT NULL and [Date Paid] IS NULL, then return the name of the month (i.e., March) from [Date of Service] field.

Also, it's possible that invoiced for 5 different months and not one of them has been paid on, therefore the formula above would need to return all 5 month's names...not just one. (i.e., March, April, May, June, July)

If you want ALL unpaid month names, you will have to write a UDF.