#Error in calculated field

[paulhenley]

I have a calculated field that calulates the last day of the following month based on a value from another field. The formula used works very well -

=DateSerial(Year([itprininvdat]),(Month([itprininvdat])+2),1)-1

The problem I have is that if there is no value in the [itprininvdat] field the calulation result shows #Error. I have always got round this by using an IIF statement, but on this occassion I get a syntax error stating that I have entered a comma without a preceding value or identifier. I think the IIF statement does not like the commas in the dateserial section, but I cannot find away around it.

Please can anyone help

 

[David R]

Make sure you are including your entire DateSerial statement inside the Iif. I get the impression you probably closed it too early, and so it is taking ",1)-1)" as the "False" case of the Iif.

HTH,
David R

 

[paulhenley]

David,
Thanks for your reply. I too thought that this must be the problem, but no matter how I modify the statement it still does not work. I detail them below if anyone can identify what I am missing -

Date serial command that works -
=DateSerial(Year([itprininvdat]),(Month([itprininvdat])+2),1)-1

IIf statement -
IIf ( [itprininvdat] > 0 , (=DateSerial(Year([itprininvdat]),(Month([itprininvdat])+2),1)-1), Null)

I am confused on this one!

 

[David R]

You're gonna kick yourself.

Take out the = in the midst of the Iif statement. It works fine for me without that (it goes before the Iif).

David R

 

[paulhenley]

David,
I wish I could buy you a pint !!

I can not believe that I missed something so obvious.

Many thanks.

 

[David R]

Man, if I had a pint of Guinness for every post I made....

You're quite welcome. Undoubtedly you'll return the favor sometime.

David R


[This message has been edited by David R (edited 04-16-2002).]