IIf with two true values (1 Viewer)

[Mhelp]

Can I generate an IIf statement with two-true values?

I have these two statements but don´t know how to combine them:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;””)
IIf((Weekday(date);1)=vbSaturday or vbSunday;y;””)

I have at field with a date and will like the true statement to be “x” if the data is Monday-Friday and “y” if the date are a Saturday or Sunday and if the date field is blank, I want to have a blank field here as well.

I have tried:
IIf((Weekday(date);1)=vbMonday or vbTuesday or vbWednesday or vbThursday or vbFriday;x;y)
But it returns y in fields that should be blank

EDIT: In the Danish version of Access it is ";" and not ":" inbetween

 

[marlan]

Hi,
Use a nested IIF function:
IIF(IsNull (Date),Null, IIF(date=...,X,Y)).

BTW, Date is a saved word, better practice to give your Variables/fields/Controls other names.

 

[CazB]

Is this in a function, or in a query?

IN a query, this should work.....

=IIf(Weekday([yourdate],0) Between 1 And 5,"X",IIf(Weekday([yourdate],0)<=7,"Y",Null))

OR

=IIf(isnull(yourdate),null,iif(Weekday([yourdate],0) Between 1 And 5,"X","Y"))


should work similarly in a function?

 

[JHB]

..
EDIT: In the Danish version of Access it is ";" and not ":" inbetween

Not quite right, In the Danish version of Access it is ";" and not "," inbetween

 

[CazB]

so in Danish, it would be

=IIf(Weekday([yourdate];0) Between 1 And 5;"X";IIf(Weekday([yourdate];0)<=7;"Y";Null))

or

=IIf(isnull(yourdate);null;iif(Weekday([yourdate];0) Between 1 And 5;"X";"Y"))

?

weird

 

[Mhelp]

It is a query


Thanks it works when I use an existing date from my tables and simple X and Y as true.

However I would like also to add that the person still is alive:
[2]![Status]="alive"
My dates are generate via a baseline date from table “2”:
Mydate = DateAdd("d";12;[2]![Baseline])
X= DateAdd("d";12;[2]![Baseline])
Y= DateAdd("d";14;[2]![Baseline])

I would think it should look like this:

IIf([2]![Status]="alive" And (Weekday(DateAdd("d";12;[2]![Baseline]);0)) Between "1" And "5";DateAdd("d";12;[2]![Baseline]);IIf(Weekday(DateAdd("d";12;[2]![Baseline]);0)<=7;DateAdd("d";14;[2]![Baseline]); ""))

However this does not work, I get dates even when patient’s status is not “alive”
(I´m working with incurable cancer patients if it sounds a little weird)

 

[CazB]

Just put your "if they're alive' criteria around the other ifs....

using my example above:

=iif(status="alive";IIf(Weekday([yourdate];0) Between 1 And 5;"X";IIf(Weekday([yourdate];0)<=7;"Y";Null));Null)

 

[Mhelp]

Thanks.. It works!!