IIf validation rule

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello
there is a form A (--name--surname---speciality--) and a subform
B(--X--Y--Z--)
the speciality field is a combo box with some standard values...
i want to input a validation rule at the --Z-- field using the IIf function
which define
the Z (euros) <50 if "speciality"=AA , Z<30 if "speciality"=BB and Z<10
if "speciality"=CC ...
I tried some but without success.
if anybody can help...
 
suggest you add a column to the Specialty combo box, with the values you
want to check against for each specialty record, as

Column0 Column1
AA 30
BB 10
CC 50

and so on. note that combo box columns are zero-based, so the first column
is zero (0), the second column is (1), the third column is (2), etc.
increase value in the ColumnCount property of the combo box control by one,
to account for the added column. in the ColumnWidths property, add

; 0"

to the end of the value, to set the width of the new column to zero, so it's
not visible in the "droplist".

now you can set a validation rule on the Z control in the subform to

<Forms!MainFormName!ComboboxName.Column(1)

if you have more than two columns in the combo box control, adjust the
column reference from (1) to the correct number for the added column.

hth
 
Thanks Tina its working but im still curious how i can use the IIf function
with <> in a form :) ...

actually i dont know if its possible
 
the situation you described would call for multiple, nested IIf() functions,
which would not dynamically adjust to any change you made in the combobox
list of values - which is why i basically ignored your question in favor of
a solution that would better fit the situation and require no programming
maintenance.

but to answer your actual question... :)
nested IIf functions follow a simple enough logic, but you have to think
through the logic very carefully. consider that the basic "English" for an
IIf function is

If this is true, then do that, otherwise do something else.

try looking at it as a hierarchy of actions, as

If this is true
then do that
otherwise do something else.

so you have to decide what you want to happen when "this" is true. if you
need to choose between two things, then you'd nest another IIf function, as

If this is true
then if this is true
then do that
otherwise do something else
otherwise do something else

the actual syntax would be

IIf(This = True, IIf(This = True, X, Y), Z)

or perhaps if this is true then you want one thing to happen, otherwise you
want to choose between two other actions, as

If this is true
then do that
otherwise if this is true
then do that
otherwise do something else

here, the actual syntax would be

IIf(This = True, X, IIf(This = True, Y, Z))

you can write multiple nesting levels in IIf functions, but i think the
above examples are enough to give you the basic idea. in reality, nested IIf
functions can become very complex and difficult to write and debug.
depending on what you're doing, the Choose function or the Switch function
might be a better choice - you can read up on those functions in Access
Help. i find it much easier to write nested If statements in VBA code, or
use the Select Case statement in VBA code, to handle complex operations that
are dependent on a runtime value.

hth
 
Hello Tina
sorry for bothering u again but yours idea works better and i would like to
ask you something more...
If i add another column at he combobox (a third one)...is it possible the
validation rule works with the column(1) when a 1/1/06<[date]<30/6/06 value
and when
1/7/06<[date]<31/12/06 the column (2) take affect? The [data] field belong
at the subform(B). I think im asking too much but i think its deserve a
chance. thanks
 
1/1/06<[date]<30/6/06

this expression makes no sense to me. try saying it as a sentence in English
(the way i did with the IIf function) to help me understand what you're
trying to do.

hth


chill said:
Hello Tina
sorry for bothering u again but yours idea works better and i would like to
ask you something more...
If i add another column at he combobox (a third one)...is it possible the
validation rule works with the column(1) when a 1/1/06<[date]<30/6/06 value
and when
1/7/06<[date]<31/12/06 the column (2) take affect? The [data] field belong
at the subform(B). I think im asking too much but i think its deserve a
chance. thanks

tina said:
the situation you described would call for multiple, nested IIf() functions,
which would not dynamically adjust to any change you made in the combobox
list of values - which is why i basically ignored your question in favor of
a solution that would better fit the situation and require no programming
maintenance.

but to answer your actual question... :)
nested IIf functions follow a simple enough logic, but you have to think
through the logic very carefully. consider that the basic "English" for an
IIf function is

If this is true, then do that, otherwise do something else.

try looking at it as a hierarchy of actions, as

If this is true
then do that
otherwise do something else.

so you have to decide what you want to happen when "this" is true. if you
need to choose between two things, then you'd nest another IIf function, as

If this is true
then if this is true
then do that
otherwise do something else
otherwise do something else

the actual syntax would be

IIf(This = True, IIf(This = True, X, Y), Z)

or perhaps if this is true then you want one thing to happen, otherwise you
want to choose between two other actions, as

If this is true
then do that
otherwise if this is true
then do that
otherwise do something else

here, the actual syntax would be

IIf(This = True, X, IIf(This = True, Y, Z))

you can write multiple nesting levels in IIf functions, but i think the
above examples are enough to give you the basic idea. in reality, nested IIf
functions can become very complex and difficult to write and debug.
depending on what you're doing, the Choose function or the Switch function
might be a better choice - you can read up on those functions in Access
Help. i find it much easier to write nested If statements in VBA code, or
use the Select Case statement in VBA code, to handle complex operations that
are dependent on a runtime value.

hth


by
one, so
it's and
Z<10
Click to expand...
Click to expand...
 
Do you mean something like:

IIF([date1] BETWEEN 2/1/06 AND 29/06/06 AND [date2] BETWEEN 2/7/06 AND
30/12/06, result1,result2])

actually I would guess that you meant 1/1/06<= [date]<= 30/6/06

And ... There is an Access function Date(). Date is a an Access reserved
word, you should not use it as a field name [date].

chill said:
Hello Tina
sorry for bothering u again but yours idea works better and i would like
to
ask you something more...
If i add another column at he combobox (a third one)...is it possible the
validation rule works with the column(1) when a 1/1/06<[date]<30/6/06
value
and when
1/7/06<[date]<31/12/06 the column (2) take affect? The [data] field
belong
at the subform(B). I think im asking too much but i think its deserve a
chance. thanks

tina said:
the situation you described would call for multiple, nested IIf()
functions,
which would not dynamically adjust to any change you made in the combobox
list of values - which is why i basically ignored your question in favor
of
a solution that would better fit the situation and require no programming
maintenance.

but to answer your actual question... :)
nested IIf functions follow a simple enough logic, but you have to think
through the logic very carefully. consider that the basic "English" for
an
IIf function is

If this is true, then do that, otherwise do something else.

try looking at it as a hierarchy of actions, as

If this is true
then do that
otherwise do something else.

so you have to decide what you want to happen when "this" is true. if you
need to choose between two things, then you'd nest another IIf function,
as

If this is true
then if this is true
then do that
otherwise do something else
otherwise do something else

the actual syntax would be

IIf(This = True, IIf(This = True, X, Y), Z)

or perhaps if this is true then you want one thing to happen, otherwise
you
want to choose between two other actions, as

If this is true
then do that
otherwise if this is true
then do that
otherwise do something else

here, the actual syntax would be

IIf(This = True, X, IIf(This = True, Y, Z))

you can write multiple nesting levels in IIf functions, but i think the
above examples are enough to give you the basic idea. in reality, nested
IIf
functions can become very complex and difficult to write and debug.
depending on what you're doing, the Choose function or the Switch
function
might be a better choice - you can read up on those functions in Access
Help. i find it much easier to write nested If statements in VBA code, or
use the Select Case statement in VBA code, to handle complex operations
that
are dependent on a runtime value.

hth
Click to expand...
Click to expand...
 
IIF([date1] BETWEEN #1/2/2006# AND #06/29/2006# AND [date2] BETWEEN
#7/2/2006# AND
#12/30/2006#, result1,result2])

Dates need to be delimited with # symbols, and regardless of what the Short
Date format has been set to through Regional Settings, you need to use
mm/dd/yyyy format. (Okay, this second part isn't strictly true: it can be an
unambigous format such as yyyy-mm-dd or dd mmm yyyy)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David F Cox said:
Do you mean something like:

IIF([date1] BETWEEN 2/1/06 AND 29/06/06 AND [date2] BETWEEN 2/7/06 AND
30/12/06, result1,result2])

actually I would guess that you meant 1/1/06<= [date]<= 30/6/06

And ... There is an Access function Date(). Date is a an Access reserved
word, you should not use it as a field name [date].

chill said:
Hello Tina
sorry for bothering u again but yours idea works better and i would like
to
ask you something more...
If i add another column at he combobox (a third one)...is it possible the
validation rule works with the column(1) when a 1/1/06<[date]<30/6/06
value
and when
1/7/06<[date]<31/12/06 the column (2) take affect? The [data] field
belong
at the subform(B). I think im asking too much but i think its deserve a
chance. thanks
Click to expand...
Click to expand...
 
Thanks Douglas, I am sometimes too lazy with my "something like" clause,
though this time it was time constraints. I should not be trying to do this
when pushed for time.

Douglas J. Steele said:
IIF([date1] BETWEEN #1/2/2006# AND #06/29/2006# AND [date2] BETWEEN
#7/2/2006# AND
#12/30/2006#, result1,result2])

Dates need to be delimited with # symbols, and regardless of what the
Short Date format has been set to through Regional Settings, you need to
use mm/dd/yyyy format. (Okay, this second part isn't strictly true: it can
be an unambigous format such as yyyy-mm-dd or dd mmm yyyy)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David F Cox said:
Do you mean something like:

IIF([date1] BETWEEN 2/1/06 AND 29/06/06 AND [date2] BETWEEN 2/7/06 AND
30/12/06, result1,result2])

actually I would guess that you meant 1/1/06<= [date]<= 30/6/06

And ... There is an Access function Date(). Date is a an Access reserved
word, you should not use it as a field name [date].

chill said:
Hello Tina
sorry for bothering u again but yours idea works better and i would
like to
ask you something more...
If i add another column at he combobox (a third one)...is it possible
the
validation rule works with the column(1) when a 1/1/06<[date]<30/6/06
value
and when
1/7/06<[date]<31/12/06 the column (2) take affect? The [data] field
belong
at the subform(B). I think im asking too much but i think its deserve a
chance. thanks
Click to expand...
Click to expand...
Click to expand...
 
Where do you want to do this: in VBA, in a query, in a validation rule, or
somewhere else?

You can only use the Column collection in VBA, nowhere else.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


chill said:
Hi all and thanks for your time...
i mean exactly that but how i can combine this with the combo box...?
my combo box has 3 columns 0,1,2 (expanding Tinas idea)and i want the rule
< Forms!MainFormName!ComboboxName.Column(1)
works as [result1] and
<Forms!MainFormName!ComboboxName.Column(2)
as [result2].
do u think its realy possible or a mess ?



David F Cox said:
Thanks Douglas, I am sometimes too lazy with my "something like" clause,
though this time it was time constraints. I should not be trying to do
this
when pushed for time.

Douglas J. Steele said:
IIF([date1] BETWEEN #1/2/2006# AND #06/29/2006# AND [date2] BETWEEN
#7/2/2006# AND
#12/30/2006#, result1,result2])

Dates need to be delimited with # symbols, and regardless of what the
Short Date format has been set to through Regional Settings, you need
to
use mm/dd/yyyy format. (Okay, this second part isn't strictly true: it
can
be an unambigous format such as yyyy-mm-dd or dd mmm yyyy)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Do you mean something like:

IIF([date1] BETWEEN 2/1/06 AND 29/06/06 AND [date2] BETWEEN 2/7/06 AND
30/12/06, result1,result2])

actually I would guess that you meant 1/1/06<= [date]<= 30/6/06

And ... There is an Access function Date(). Date is a an Access
reserved
word, you should not use it as a field name [date].

Hello Tina
sorry for bothering u again but yours idea works better and i would
like to
ask you something more...
If i add another column at he combobox (a third one)...is it possible
the
validation rule works with the column(1) when a 1/1/06<[date]<30/6/06
value
and when
1/7/06<[date]<31/12/06 the column (2) take affect? The [data] field
belong
at the subform(B). I think im asking too much but i think its deserve
a
chance. thanks
Click to expand...
Click to expand...
Click to expand...
 
You still haven't told me where you're trying to use this...

It would appear that you're trying to set up a condition. You cannot put the
operator (<, =, <>, etc.) inside the IIf statement when you're using it as a
condition: it has to be outside the statement. And if this is in a query, it
don't believe it'll work even then, because queries don't know anything
about the Column collection.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


chill said:
HI Doug ...do u think that the expression below can work ?

IIF([date1] BETWEEN #1/2/2006#30/6/2006# , <
Forms!MainFormName!ComboboxName.Column(1),<
Forms!MainFormName!ComboboxName.Column(2))

Thanks



Douglas J. Steele said:
Where do you want to do this: in VBA, in a query, in a validation rule,
or
somewhere else?

You can only use the Column collection in VBA, nowhere else.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


chill said:
Hi all and thanks for your time...
i mean exactly that but how i can combine this with the combo box...?
my combo box has 3 columns 0,1,2 (expanding Tinas idea)and i want the
rule
< Forms!MainFormName!ComboboxName.Column(1)
works as [result1] and
<Forms!MainFormName!ComboboxName.Column(2)
as [result2].
do u think its realy possible or a mess ?



:

Thanks Douglas, I am sometimes too lazy with my "something like"
clause,
though this time it was time constraints. I should not be trying to do
this
when pushed for time.

message
IIF([date1] BETWEEN #1/2/2006# AND #06/29/2006# AND [date2] BETWEEN
#7/2/2006# AND
#12/30/2006#, result1,result2])

Dates need to be delimited with # symbols, and regardless of what
the
Short Date format has been set to through Regional Settings, you
need
to
use mm/dd/yyyy format. (Okay, this second part isn't strictly true:
it
can
be an unambigous format such as yyyy-mm-dd or dd mmm yyyy)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Do you mean something like:

IIF([date1] BETWEEN 2/1/06 AND 29/06/06 AND [date2] BETWEEN 2/7/06
AND
30/12/06, result1,result2])

actually I would guess that you meant 1/1/06<= [date]<= 30/6/06

And ... There is an Access function Date(). Date is a an Access
reserved
word, you should not use it as a field name [date].

Hello Tina
sorry for bothering u again but yours idea works better and i
would
like to
ask you something more...
If i add another column at he combobox (a third one)...is it
possible
the
validation rule works with the column(1) when a
1/1/06<[date]<30/6/06
value
and when
1/7/06<[date]<31/12/06 the column (2) take affect? The [data]
field
belong
at the subform(B). I think im asking too much but i think its
deserve
a
chance. thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top