iif expression in multiple table query

[Beejer]

I'm currently having a hard time getting my expression to run in my
billing query.

My setup:
MS Access 2003
Qry Billing (includes 6 tables such as Client, Price list, Formula,
Order, Order detail and Billing)
This query helps to create our invoice forms.

My problem:
Our Government has been so kind enough to reduce our sales tax by 1% as
per the 1st of July 2006. But this nice gesture has been giving me
headaches. In my query, I've applied the tax rate manually like so
[TPS RATE: 0.07]

I initially tried going through VB but I didn't manage to get
fonctionnal end result.

What I wish to do is to implement a conditionnal rate that will take
into consideration the date change. I've tried many option all around
the following suggestion but nothing seems to be working. (#38899 is
the equivalent number for July 1st 2006).

TPS RATE: iff( ([Billing]![BILLING DATE] < Date(38899)),"0.07",
"0.06")

Since a rate of 7% applies prior to july 1st and becomes 6% (july 1st
included). I want the system to manage the rate change.

Regards

[AP]

Hello
try using CVDATE(XXX) instead of DATE()
Beejer wrote:

I'm currently having a hard time getting my expression to run in my
billing query.

My setup:
MS Access 2003
Qry Billing (includes 6 tables such as Client, Price list, Formula,
Order, Order detail and Billing)
This query helps to create our invoice forms.

My problem:
Our Government has been so kind enough to reduce our sales tax by 1% as
per the 1st of July 2006. But this nice gesture has been giving me
headaches. In my query, I've applied the tax rate manually like so
[TPS RATE: 0.07]

I initially tried going through VB but I didn't manage to get
fonctionnal end result.

What I wish to do is to implement a conditionnal rate that will take
into consideration the date change. I've tried many option all around
the following suggestion but nothing seems to be working. (#38899 is
the equivalent number for July 1st 2006).

TPS RATE: iff( ([Billing]![BILLING DATE] < Date(38899)),"0.07",
"0.06")

Since a rate of 7% applies prior to july 1st and becomes 6% (july 1st
included). I want the system to manage the rate change.

Regards

[Keith Wilby]

"Beejer" <bj********@yahoo.ca> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

What I wish to do is to implement a conditionnal rate that will take
into consideration the date change. I've tried many option all around
the following suggestion but nothing seems to be working. (#38899 is
the equivalent number for July 1st 2006).

TPS RATE: iff( ([Billing]![BILLING DATE] < Date(38899)),"0.07",
"0.06")

Since a rate of 7% applies prior to july 1st and becomes 6% (july 1st
included). I want the system to manage the rate change.

Regards

I think < Date(38899) might be the problem - what's wrong with using the
Gregorian date?

Keith.
www.keithwilby.com

[Beejer]

Thanks for the suggestion... but everything I try dosen't seem to work
in the query expression. I even tried to go thru VB editor with a
controlsource text box... no success there either. Since the system
wasn't setup to archive the invoices... the rate will be changed
manually in the query on the effective date.

I'm still going to try to make it work for my personnal satisfaction.

Thanks for the help.

Beejer
AP a écrit :

Hello
try using CVDATE(XXX) instead of DATE()
Beejer wrote:

I'm currently having a hard time getting my expression to run in my
billing query.

My setup:
MS Access 2003
Qry Billing (includes 6 tables such as Client, Price list, Formula,
Order, Order detail and Billing)
This query helps to create our invoice forms.

My problem:
Our Government has been so kind enough to reduce our sales tax by 1% as
per the 1st of July 2006. But this nice gesture has been giving me
headaches. In my query, I've applied the tax rate manually like so
[TPS RATE: 0.07]

I initially tried going through VB but I didn't manage to get
fonctionnal end result.

What I wish to do is to implement a conditionnal rate that will take
into consideration the date change. I've tried many option all around
the following suggestion but nothing seems to be working. (#38899 is
the equivalent number for July 1st 2006).

TPS RATE: iff( ([Billing]![BILLING DATE] < Date(38899)),"0.07",
"0.06")

Since a rate of 7% applies prior to july 1st and becomes 6% (july 1st
included). I want the system to manage the rate change.

Regards