An expression question on dates

[Microsoft]

Good afternoon,

I am trying to makethe following expression work:

=IIf([StayStart]<"6/1/2003",".07",".0725")

This is a formula to calculae sales tax. If the date is prior to June 1,
2003 then use 7% otherwise use 7.25%. The formula is returning for the day
and month and ignoring the year. Can anyone tell me what I am doing wrong?

Many thanks,
Scott B

 

[Allen Browne]

Try changing the Name property of the control.
If it's bound to an expression, but has the same name as a field, Access
gets confused.

If that doesn't solve the problem, you have a references problem. Details:
http://users.bigpond.net.au/abrowne1/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I get the following error when I changed the expression:
#Name?

Any thoughts?

Best regards,
Scott

Use hash as the delimiter for the dates:
=IIf([StayStart]<#6/1/2003#, 0.07, 0.0725)
You probably don't want the quote marks around the numbers either: that
forces Access to think they are text.

Consider adding a field to store the tax rate with the transaction. That not
only copes with future changes in tax rates also, but also allow you to
handle tax-exempt transactions (0.00%).

It's quite easy to throw in 0.0725 for all existing transactions with an
update query (Update on Query menu in query design).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Good afternoon,

I am trying to makethe following expression work:

=IIf([StayStart]<"6/1/2003",".07",".0725")

This is a formula to calculae sales tax. If the date is prior to June 1,
2003 then use 7% otherwise use 7.25%. The formula is returning for

the
day

and month and ignoring the year. Can anyone tell me what I am doing

wrong?

 

[Microsoft]

Hello Allen,

I tried changing the name and it did not help. I went into References and
checked off the ADO and DAO choices. It still does not work. I am a little
baffled. I know that no control can have the same name as on object. So if
you have any other thoughts I am all ears.

Also, I agreet that I need to have a field to store the tax rate for future
use and I need it to create invoices. Do yuo have a suggstion as to how I
would store the rate? Can I use the already existing expression I am using
to calculate the rate for the form? What would it look like. Can I start
here?

=IIf([Tax Exempt], 0, (IIF([StayStart]<#6/1/2003#, [Sales_Tax_Rate] = 0.07,
[Sales_Tax_Rate] = 0.0725))

Best regards,
Scott B

Try changing the Name property of the control.
If it's bound to an expression, but has the same name as a field, Access
gets confused.

If that doesn't solve the problem, you have a references problem. Details:
http://users.bigpond.net.au/abrowne1/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I get the following error when I changed the expression:
#Name?

Any thoughts?

Best regards,
Scott

Use hash as the delimiter for the dates:
=IIf([StayStart]<#6/1/2003#, 0.07, 0.0725)
You probably don't want the quote marks around the numbers either: that
forces Access to think they are text.

Consider adding a field to store the tax rate with the transaction.

That
not

only copes with future changes in tax rates also, but also allow you to
handle tax-exempt transactions (0.00%).

It's quite easy to throw in 0.0725 for all existing transactions with an
update query (Update on Query menu in query design).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Good afternoon,

I am trying to makethe following expression work:

=IIf([StayStart]<"6/1/2003",".07",".0725")

This is a formula to calculae sales tax. If the date is prior to

June
1,

2003 then use 7% otherwise use 7.25%. The formula is returning for the
day
and month and ignoring the year. Can anyone tell me what I am doing
wrong?

 

[Allen Browne]

No, you cannot assign a value to Sales_Tax_Rate as your expression is trying
to do.

You can display the value in a text box with Control Source of:
=IIf([Tax Exempt], 0, IIF([StayStart] < #6/1/2003#, 0.07, 0.0725))

To assign the value to the Sales_Tax_Rate field, you would need to use an
Update query. The expression for the Update row would be the same.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hello Allen,

I tried changing the name and it did not help. I went into References and
checked off the ADO and DAO choices. It still does not work. I am a little
baffled. I know that no control can have the same name as on object. So if
you have any other thoughts I am all ears.

Also, I agreet that I need to have a field to store the tax rate for future
use and I need it to create invoices. Do yuo have a suggstion as to how I
would store the rate? Can I use the already existing expression I am using
to calculate the rate for the form? What would it look like. Can I start
here?

=IIf([Tax Exempt], 0, (IIF([StayStart]<#6/1/2003#, [Sales_Tax_Rate] = 0.07,
[Sales_Tax_Rate] = 0.0725))

Best regards,
Scott B

Try changing the Name property of the control.
If it's bound to an expression, but has the same name as a field, Access
gets confused.

If that doesn't solve the problem, you have a references problem. Details:
http://users.bigpond.net.au/abrowne1/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I get the following error when I changed the expression:
#Name?

Any thoughts?

Best regards,
Scott


Use hash as the delimiter for the dates:
=IIf([StayStart]<#6/1/2003#, 0.07, 0.0725)
You probably don't want the quote marks around the numbers either: that
forces Access to think they are text.

Consider adding a field to store the tax rate with the transaction. That
not
only copes with future changes in tax rates also, but also allow you to
handle tax-exempt transactions (0.00%).

It's quite easy to throw in 0.0725 for all existing transactions

with

an

update query (Update on Query menu in query design).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Good afternoon,

I am trying to makethe following expression work:

=IIf([StayStart]<"6/1/2003",".07",".0725")

This is a formula to calculae sales tax. If the date is prior to June
1,
2003 then use 7% otherwise use 7.25%. The formula is returning

for
the

day
and month and ignoring the year. Can anyone tell me what I am doing
wrong?

 

[Allen Browne]

Scott, you have a table with data in it.
You are adding a sales tax rate field to that table.
The new field doesn't have any data in it.
You want to run an update query to put a value in to the field for all
existing records.

Create the query into this table.
Change it to an update query.
Put the expression into the Update row under the new field.
Run the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I hate being this dense, but where does the data for the query come from?
What do I base the query on?

Best regards,
Scott B

No, you cannot assign a value to Sales_Tax_Rate as your expression is trying
to do.

You can display the value in a text box with Control Source of:
=IIf([Tax Exempt], 0, IIF([StayStart] < #6/1/2003#, 0.07, 0.0725))

To assign the value to the Sales_Tax_Rate field, you would need to use an
Update query. The expression for the Update row would be the same.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hello Allen,

I tried changing the name and it did not help. I went into References and
checked off the ADO and DAO choices. It still does not work. I am a little
baffled. I know that no control can have the same name as on object.

So
if

you have any other thoughts I am all ears.

Also, I agreet that I need to have a field to store the tax rate for future
use and I need it to create invoices. Do yuo have a suggstion as to

how

I

would store the rate? Can I use the already existing expression I am using
to calculate the rate for the form? What would it look like. Can I start
here?

=IIf([Tax Exempt], 0, (IIF([StayStart]<#6/1/2003#, [Sales_Tax_Rate] = 0.07,
[Sales_Tax_Rate] = 0.0725))

Best regards,
Scott B


Try changing the Name property of the control.
If it's bound to an expression, but has the same name as a field, Access
gets confused.

If that doesn't solve the problem, you have a references problem. Details:
http://users.bigpond.net.au/abrowne1/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I get the following error when I changed the expression:
#Name?

Any thoughts?

Best regards,
Scott


Use hash as the delimiter for the dates:
=IIf([StayStart]<#6/1/2003#, 0.07, 0.0725)
You probably don't want the quote marks around the numbers either:
that
forces Access to think they are text.

Consider adding a field to store the tax rate with the transaction.
That
not
only copes with future changes in tax rates also, but also allow you
to
handle tax-exempt transactions (0.00%).

It's quite easy to throw in 0.0725 for all existing transactions with
an
update query (Update on Query menu in query design).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Good afternoon,

I am trying to makethe following expression work:

=IIf([StayStart]<"6/1/2003",".07",".0725")

This is a formula to calculae sales tax. If the date is prior to
June
1,
2003 then use 7% otherwise use 7.25%. The formula is

returning
for

the
day
and month and ignoring the year. Can anyone tell me what I am doing
wrong?

 

[Microsoft]

Allen,

Zippity Doo Dah that's slick. Worked like a charm. One more question. Can
I update the data in the table with the query when I update or add a record.
I think I need to create a macro to run the query on a specific event. What
event would you suggest if this is a good idea.

Best regards,
Scott

Scott, you have a table with data in it.
You are adding a sales tax rate field to that table.
The new field doesn't have any data in it.
You want to run an update query to put a value in to the field for all
existing records.

Create the query into this table.
Change it to an update query.
Put the expression into the Update row under the new field.
Run the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I hate being this dense, but where does the data for the query come from?
What do I base the query on?

Best regards,
Scott B

No, you cannot assign a value to Sales_Tax_Rate as your expression is trying
to do.

You can display the value in a text box with Control Source of:
=IIf([Tax Exempt], 0, IIF([StayStart] < #6/1/2003#, 0.07, 0.0725))

To assign the value to the Sales_Tax_Rate field, you would need to use an
Update query. The expression for the Update row would be the same.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hello Allen,

I tried changing the name and it did not help. I went into

References
and

checked off the ADO and DAO choices. It still does not work. I am a
little
baffled. I know that no control can have the same name as on object. So
if
you have any other thoughts I am all ears.

Also, I agreet that I need to have a field to store the tax rate for
future
use and I need it to create invoices. Do yuo have a suggstion as to

how

I
would store the rate? Can I use the already existing expression I am
using
to calculate the rate for the form? What would it look like. Can I start
here?

=IIf([Tax Exempt], 0, (IIF([StayStart]<#6/1/2003#, [Sales_Tax_Rate] =
0.07,
[Sales_Tax_Rate] = 0.0725))

Best regards,
Scott B


Try changing the Name property of the control.
If it's bound to an expression, but has the same name as a field, Access
gets confused.

If that doesn't solve the problem, you have a references problem.
Details:
http://users.bigpond.net.au/abrowne1/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I get the following error when I changed the expression:
#Name?

Any thoughts?

Best regards,
Scott


Use hash as the delimiter for the dates:
=IIf([StayStart]<#6/1/2003#, 0.07, 0.0725)
You probably don't want the quote marks around the numbers either:
that
forces Access to think they are text.

Consider adding a field to store the tax rate with the transaction.
That
not
only copes with future changes in tax rates also, but also

allow
you

to
handle tax-exempt transactions (0.00%).

It's quite easy to throw in 0.0725 for all existing transactions
with
an
update query (Update on Query menu in query design).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Good afternoon,

I am trying to makethe following expression work:

=IIf([StayStart]<"6/1/2003",".07",".0725")

This is a formula to calculae sales tax. If the date is

prior
to

June
1,
2003 then use 7% otherwise use 7.25%. The formula is returning
for
the
day
and month and ignoring the year. Can anyone tell me what I am
doing
wrong?

 

[Allen Browne]

Use the AfterUpdate event procedure of the control, as discussed earlier.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

Zippity Doo Dah that's slick. Worked like a charm. One more question. Can
I update the data in the table with the query when I update or add a record.
I think I need to create a macro to run the query on a specific event. What
event would you suggest if this is a good idea.

Best regards,
Scott

Scott, you have a table with data in it.
You are adding a sales tax rate field to that table.
The new field doesn't have any data in it.
You want to run an update query to put a value in to the field for all
existing records.

Create the query into this table.
Change it to an update query.
Put the expression into the Update row under the new field.
Run the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I hate being this dense, but where does the data for the query come from?
What do I base the query on?

Best regards,
Scott B


No, you cannot assign a value to Sales_Tax_Rate as your expression is
trying
to do.

You can display the value in a text box with Control Source of:
=IIf([Tax Exempt], 0, IIF([StayStart] < #6/1/2003#, 0.07, 0.0725))

To assign the value to the Sales_Tax_Rate field, you would need to

use
an

Update query. The expression for the Update row would be the same.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hello Allen,

I tried changing the name and it did not help. I went into References
and
checked off the ADO and DAO choices. It still does not work. I

am

a

little
baffled. I know that no control can have the same name as on

object.
So

if
you have any other thoughts I am all ears.

Also, I agreet that I need to have a field to store the tax rate for
future
use and I need it to create invoices. Do yuo have a suggstion as

to
how

I
would store the rate? Can I use the already existing expression I am
using
to calculate the rate for the form? What would it look like. Can I
start
here?

=IIf([Tax Exempt], 0, (IIF([StayStart]<#6/1/2003#,

[Sales_Tax_Rate]

=

0.07,
[Sales_Tax_Rate] = 0.0725))

Best regards,
Scott B


Try changing the Name property of the control.
If it's bound to an expression, but has the same name as a field,
Access
gets confused.

If that doesn't solve the problem, you have a references problem.
Details:
http://users.bigpond.net.au/abrowne1/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Allen,

I get the following error when I changed the expression:
#Name?

Any thoughts?

Best regards,
Scott


Use hash as the delimiter for the dates:
=IIf([StayStart]<#6/1/2003#, 0.07, 0.0725)
You probably don't want the quote marks around the numbers either:
that
forces Access to think they are text.

Consider adding a field to store the tax rate with the
transaction.
That
not
only copes with future changes in tax rates also, but also allow
you
to
handle tax-exempt transactions (0.00%).

It's quite easy to throw in 0.0725 for all existing transactions
with
an
update query (Update on Query menu in query design).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Good afternoon,

I am trying to makethe following expression work:

=IIf([StayStart]<"6/1/2003",".07",".0725")

This is a formula to calculae sales tax. If the date is prior
to
June
1,
2003 then use 7% otherwise use 7.25%. The formula is returning
for
the
day
and month and ignoring the year. Can anyone tell me what

I

am