Formula Error

  • Thread starter Thread starter Tina C.
  • Start date Start date
T

Tina C.

Hi,

I am having trouble with a formula from a reply to my post
on 12/31. The formula: Tax-D: [Quantity] * Choose([X]
= "Over", .3, [X] = "Under", .12, [X] =
"Diff", NULL)

Here is the formula I used: Tax: [Quantity]*Choose
([StateTax]="Over",0.3,[StateTax]="Under",0.12)
The "Tax" appears as the column heading but the remainder
is blank. I don't know what I am doing wrong. Please
help!

Here is the original post and reply:
Hello,

I need help with two things in Access 2000:
1. I would like the date in the query (now shown as
mm/dd/yyyy)to appear as month or to run the query based on
the month only.
Click to expand...

Regardless of how it's displayed, a Date/Time field is
stored
internally as a number ( a count of days since an
arbitrary start
point, December 30, 1899). If you want to see that date as
a month
only simply use a Format property of "mmmm" (for
December, "mmm" for
Dec) on the Textbox in which you display the data. (Do use
a Form for
onscreen display, or a Report for printing; query
datasheets are VERY
limited in their capability).
2. In the same query - In column "X", the options are
Over, Under, or Diff. I need a If/then function to say if
the item is over in X then mutiply quantity by .3,if it is
under then multiply by .12, or if diff. then nothing.
(eg - item1 (Product-a) 12 (quantity-b) over (x-c) =12*.3
(tax-d)
Click to expand...

The Choose() function is helpful here: it takes any number
of pairs of
arguments and loops through them. If the first argument of
a pair is
True it returns the other member of that pair. An
expression like

Tax-D: [Quantity] * Choose([X] = "Over", .3, [X]
= "Under", .12, [X] =
"Diff", NULL)

depending on what you mean by "nothing" - using NULL will
display a
blank no matter what is in Quantity.
 
Replace Choose with Switch in the statement:

Tax: [Quantity]*Switch([StateTax]="Over",0.3,[StateTax]="Under",0.12)

--
Ken Snell
<MS ACCESS MVP>

Tina C. said:
Hi,

I am having trouble with a formula from a reply to my post
on 12/31. The formula: Tax-D: [Quantity] * Choose([X]
= "Over", .3, [X] = "Under", .12, [X] =
"Diff", NULL)

Here is the formula I used: Tax: [Quantity]*Choose
([StateTax]="Over",0.3,[StateTax]="Under",0.12)
The "Tax" appears as the column heading but the remainder
is blank. I don't know what I am doing wrong. Please
help!

Here is the original post and reply:
Hello,

I need help with two things in Access 2000:
1. I would like the date in the query (now shown as
mm/dd/yyyy)to appear as month or to run the query based on
the month only.
Click to expand...

Regardless of how it's displayed, a Date/Time field is
stored
internally as a number ( a count of days since an
arbitrary start
point, December 30, 1899). If you want to see that date as
a month
only simply use a Format property of "mmmm" (for
December, "mmm" for
Dec) on the Textbox in which you display the data. (Do use
a Form for
onscreen display, or a Report for printing; query
datasheets are VERY
limited in their capability).
2. In the same query - In column "X", the options are
Over, Under, or Diff. I need a If/then function to say if
the item is over in X then mutiply quantity by .3,if it is
under then multiply by .12, or if diff. then nothing.
(eg - item1 (Product-a) 12 (quantity-b) over (x-c) =12*.3
(tax-d)
Click to expand...

The Choose() function is helpful here: it takes any number
of pairs of
arguments and loops through them. If the first argument of
a pair is
True it returns the other member of that pair. An
expression like

Tax-D: [Quantity] * Choose([X] = "Over", .3, [X]
= "Under", .12, [X] =
"Diff", NULL)

depending on what you mean by "nothing" - using NULL will
display a
blank no matter what is in Quantity.
Click to expand...
 
Worked like a charm!!!

Thanks,

Tina
-----Original Message-----
Replace Choose with Switch in the statement:

Tax: [Quantity]*Switch([StateTax]="Over",0.3,[StateTax] ="Under",0.12)

--
Ken Snell
<MS ACCESS MVP>

Tina C. said:
Hi,

I am having trouble with a formula from a reply to my post
on 12/31. The formula: Tax-D: [Quantity] * Choose([X]
= "Over", .3, [X] = "Under", .12, [X] =
"Diff", NULL)

Here is the formula I used: Tax: [Quantity]*Choose
([StateTax]="Over",0.3,[StateTax]="Under",0.12)
The "Tax" appears as the column heading but the remainder
is blank. I don't know what I am doing wrong. Please
help!

Here is the original post and reply:
Hello,

I need help with two things in Access 2000:
1. I would like the date in the query (now shown as
mm/dd/yyyy)to appear as month or to run the query based on
the month only.
Click to expand...

Regardless of how it's displayed, a Date/Time field is
stored
internally as a number ( a count of days since an
arbitrary start
point, December 30, 1899). If you want to see that date as
a month
only simply use a Format property of "mmmm" (for
December, "mmm" for
Dec) on the Textbox in which you display the data. (Do use
a Form for
onscreen display, or a Report for printing; query
datasheets are VERY
limited in their capability).
2. In the same query - In column "X", the options are
Over, Under, or Diff. I need a If/then function to say if
the item is over in X then mutiply quantity by .3,if it is
under then multiply by .12, or if diff. then nothing.
(eg - item1 (Product-a) 12 (quantity-b) over (x-c) =12*.3
(tax-d)
Click to expand...

The Choose() function is helpful here: it takes any number
of pairs of
arguments and loops through them. If the first argument of
a pair is
True it returns the other member of that pair. An
expression like

Tax-D: [Quantity] * Choose([X] = "Over", .3, [X]
= "Under", .12, [X] =
"Diff", NULL)

depending on what you mean by "nothing" - using NULL will
display a
blank no matter what is in Quantity.
Click to expand...


.
Click to expand...
 
The formula: Tax-D: [Quantity] * Choose([X]
Click to expand...

My apologies! I misremembered the two rather similar functions Choose
and Switch - it's Switch that you need here instead of Choose.

Sorry for the wasted time and effort!
 
Back
Top