IIF Function error in Access 2003

Hi,

Following on from my separate post yesterday regarding issues I'm having
after creating a database in Access 2007 which some users need to use from
Access 2003.

I have the following IIF function working perfectly in a form in Access 2007
which is now returning a #Name? error in 2003 when the same form/database is
opened with 2003 and I have no idea why?

=IIf([ContractEndDate] Between Date() And Date()+90,"To Be
Renewed",IIf([ContractEndDate]Date()+90,"In
Contract",IIf([ContractEndDate]Date(),"Contract Expired","Start/End Date
Missing")))

As I said, this formula is working perfectly in 2007. Any suggestions would
be greatly appreciated. I have tried to recreate the formula from scratch in
a new field and still get the #Name? error. It is not a typo and the field
"ContractEndDate" is part of a query used to populate the form and is there
on the form. What could be causing this???

I will NEVER develop in 2007 again if any users are going to be running
access 2003!!!!!!!!!!!!!!!!!

Many thanks

Steve Muir

In Access 2003 I need to do something like this:

=IIf([ContractEndDate] = Date() And [ContractEndDate] = Date()+90, etc.

A possible simplification:

=IIf([ContractEndDate] Is Null, "Start/End Date Missing", _
IIf([ContractEndDate] Date() + 90,"In Contract", _
IIf([ContractEndDate]Date(),"Contract Expired", _
"To Be Renewed")))

Line continuation characters (underscores) are for clarity here only. They
are only for VBA, and would not be in an expression otherwise.


Steve Muir wrote:
Hi,

Following on from my separate post yesterday regarding issues I'm having
after creating a database in Access 2007 which some users need to use from
Access 2003.

I have the following IIF function working perfectly in a form in Access 2007
which is now returning a #Name? error in 2003 when the same form/database is
opened with 2003 and I have no idea why?

=IIf([ContractEndDate] Between Date() And Date()+90,"To Be
Renewed",IIf([ContractEndDate]Date()+90,"In
Contract",IIf([ContractEndDate]Date(),"Contract Expired","Start/End Date
Missing")))

As I said, this formula is working perfectly in 2007. Any suggestions would
be greatly appreciated. I have tried to recreate the formula from scratch in
a new field and still get the #Name? error. It is not a typo and the field
"ContractEndDate" is part of a query used to populate the form and is there
on the form. What could be causing this???

I will NEVER develop in 2007 again if any users are going to be running
access 2003!!!!!!!!!!!!!!!!!

Many thanks

Steve Muir

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1