Prev: Global value
Next: Insert data into Combo
From: Steve Muir on 16 Mar 2010 07:31
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
From: BruceM via AccessMonster.com on 16 Mar 2010 08:00
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/Forums.aspx/access-forms/201003/1

 | 
Pages: 1
Prev: Global value
Next: Insert data into Combo