IIF not working

[CJ]

Hi groupies.

So for us in Canada we have a new GST rate of 6% effective July 1. I am
trying to write an IIF statement as a calculation in a text box to check a
date and if the date is >June 30 2006 then apply 6% otherwise 7%.

Here is what I have....but everything is still 7%.

=IIf([subProgress].[Form]![ToDate]>"30/06/2006" And
[txtExempt]="No",[txtToDate]*0.06,IIf([subProgress].[Form]![ToDate]<"30/06/2006"
And [txtExempt]="No",[txtToDate]*0.07,[txtToDate]*0))

My text box is on a tab control on the parent form....the date is on a
subform of the parent.

Thanks
CJ

 

[pietlinden]

CJ,

Some fun reading for you... actually very helpful and succint.
http://www.mvps.org/access/forms/frm0031.htm
http://www.mvps.org/access/forms/frm0025.htm

 

[Tom Lake]

Hi groupies.

So for us in Canada we have a new GST rate of 6% effective July 1. I am
trying to write an IIF statement as a calculation in a text box to check a
date and if the date is >June 30 2006 then apply 6% otherwise 7%.

Here is what I have....but everything is still 7%.

Is ToDate a date field? If so, you have to test it in US format no matter
what the
settings are. Also, you only have two possibilities for dates: > June 30 or
not. You don't
need to test both. Try the below
=IIf([txtExempt]="No", IIf([subProgress].[Form]![ToDate]>= #07/01/2006#,
[txtToDate]*0.06, [txtToDate]*0.07), 0)

Tom Lake

 

[Douglas J. Steele]

Just wanted to highlight the fact that not only did Tom correctly change the
format to mm/dd/yyyy, but he also changed the delimiter from " to #.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi groupies.

So for us in Canada we have a new GST rate of 6% effective July 1. I am
trying to write an IIF statement as a calculation in a text box to check
a date and if the date is >June 30 2006 then apply 6% otherwise 7%.

Here is what I have....but everything is still 7%.

Is ToDate a date field? If so, you have to test it in US format no matter
what the
settings are. Also, you only have two possibilities for dates: > June 30
or not. You don't
need to test both. Try the below
=IIf([txtExempt]="No", IIf([subProgress].[Form]![ToDate]>= #07/01/2006#,
[txtToDate]*0.06, [txtToDate]*0.07), 0)

Tom Lake

 

[CJ]

Brilliant, thanks a bunch everybody!!

Just wanted to highlight the fact that not only did Tom correctly change
the format to mm/dd/yyyy, but he also changed the delimiter from " to #.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi groupies.

So for us in Canada we have a new GST rate of 6% effective July 1. I am
trying to write an IIF statement as a calculation in a text box to check
a date and if the date is >June 30 2006 then apply 6% otherwise 7%.

Here is what I have....but everything is still 7%.

Is ToDate a date field? If so, you have to test it in US format no
matter what the
settings are. Also, you only have two possibilities for dates: > June 30
or not. You don't
need to test both. Try the below
=IIf([txtExempt]="No", IIf([subProgress].[Form]![ToDate]>= #07/01/2006#,
[txtToDate]*0.06, [txtToDate]*0.07), 0)

Tom Lake