If Statement calculation

[Guest]

I need to calculate a number based on an if statement. The calculation
determines a royalty. I know how the calculation works as a javascript, but
not in Access. For starters, where do I place the statement?
My javascript is:
var c = this.getField("Category")
var d = this.getField("Sales")

{
if
(c.value == " ")
(event.value = 0);
else

if
(c.value == "Food")
(event.value = d.value * 0.04);
if
(c.value == "Health & Beauty")
(event.value = d.value * 0.06);
else

(event.value = d.value * 0.13);
}

Cheers,
Graeme

 

[Wayne Morgan]

Create a "calculated field" in the query and use a nested IIf() function or
the Switch() function. Also, would Category = " ", "", or Null? There is a
difference. Access won't store " ", it trims trailing spaces automatically
when you save the value.

Example (in design grid):
EventValue:Switch(IsNull([Category]), 0, [Category]="Food", [Sales]*0.04,
[Category]="Health & Beauty", [Sales]*0.06, True, [Sales]*0.13)

Switch() evaluates from left to right and returns the value associated with
the FIRST true statement. This is probably easier to read than a nested
IIf() would be.

 

[Guest]

Thanks,
to make things more complicated what if I had "CompanyID" thrown into the
mix as well?
i.e. If [Category]="Food", [Sales]*0.04,

[Category]="Health & Beauty", [Sales]*0.06,

as before but now if [CompanyID] ="1", [Sales]*0.1,
however the lower varaiable is to apply (e.g. if category = food and
CompanyID = 1 the rate to apply is 0.04)

Create a "calculated field" in the query and use a nested IIf() function or
the Switch() function. Also, would Category = " ", "", or Null? There is a
difference. Access won't store " ", it trims trailing spaces automatically
when you save the value.

Example (in design grid):
EventValue:Switch(IsNull([Category]), 0, [Category]="Food", [Sales]*0.04,
[Category]="Health & Beauty", [Sales]*0.06, True, [Sales]*0.13)

Switch() evaluates from left to right and returns the value associated with
the FIRST true statement. This is probably easier to read than a nested
IIf() would be.

--
Wayne Morgan
MS Access MVP

I need to calculate a number based on an if statement. The calculation
determines a royalty. I know how the calculation works as a javascript,
but
not in Access. For starters, where do I place the statement?
My javascript is:
var c = this.getField("Category")
var d = this.getField("Sales")

{
if
(c.value == " ")
(event.value = 0);
else

if
(c.value == "Food")
(event.value = d.value * 0.04);
if
(c.value == "Health & Beauty")
(event.value = d.value * 0.06);
else

(event.value = d.value * 0.13);
}

Cheers,
Graeme

 

[Guest]

Thanks Wayne,
I figured out the solution to my second query (same logic!!)
That's a neat function!

Create a "calculated field" in the query and use a nested IIf() function or
the Switch() function. Also, would Category = " ", "", or Null? There is a
difference. Access won't store " ", it trims trailing spaces automatically
when you save the value.

Example (in design grid):
EventValue:Switch(IsNull([Category]), 0, [Category]="Food", [Sales]*0.04,
[Category]="Health & Beauty", [Sales]*0.06, True, [Sales]*0.13)

Switch() evaluates from left to right and returns the value associated with
the FIRST true statement. This is probably easier to read than a nested
IIf() would be.

--
Wayne Morgan
MS Access MVP

I need to calculate a number based on an if statement. The calculation
determines a royalty. I know how the calculation works as a javascript,
but
not in Access. For starters, where do I place the statement?
My javascript is:
var c = this.getField("Category")
var d = this.getField("Sales")

{
if
(c.value == " ")
(event.value = 0);
else

if
(c.value == "Food")
(event.value = d.value * 0.04);
if
(c.value == "Health & Beauty")
(event.value = d.value * 0.06);
else

(event.value = d.value * 0.13);
}

Cheers,
Graeme

 

[Guest]

Another question related to this.
Can I make that calculated field a normal field in a table? (I want to
format it so that it is a currency).
I tried associating the calculated field to a table in design view but it
did not like that. Kinda messed up the function.
Not the end of the world if I can't.
Thanks again.

Thanks Wayne,
I figured out the solution to my second query (same logic!!)
That's a neat function!

Create a "calculated field" in the query and use a nested IIf() function or
the Switch() function. Also, would Category = " ", "", or Null? There is a
difference. Access won't store " ", it trims trailing spaces automatically
when you save the value.

Example (in design grid):
EventValue:Switch(IsNull([Category]), 0, [Category]="Food", [Sales]*0.04,
[Category]="Health & Beauty", [Sales]*0.06, True, [Sales]*0.13)

Switch() evaluates from left to right and returns the value associated with
the FIRST true statement. This is probably easier to read than a nested
IIf() would be.

--
Wayne Morgan
MS Access MVP

I need to calculate a number based on an if statement. The calculation
determines a royalty. I know how the calculation works as a javascript,
but
not in Access. For starters, where do I place the statement?
My javascript is:
var c = this.getField("Category")
var d = this.getField("Sales")

{
if
(c.value == " ")
(event.value = 0);
else

if
(c.value == "Food")
(event.value = d.value * 0.04);
if
(c.value == "Health & Beauty")
(event.value = d.value * 0.06);
else

(event.value = d.value * 0.13);
}

Cheers,
Graeme

 

[Wayne Morgan]

Access tables do not support calculated fields. You will need to do that in
the query. You can format the output to look like whatever you want. While
this can be done in the query, you would usually do it in the textbox on the
form or report that is displaying the query's output. To do it in the query,
wrap the equation with the Format() function or in query design view, right
click the field and choose Properties. There will be a format property
available there.

You would not normally want to store calculated values, just calculate them
when you need them. Calculated values are based on other, "real" values and
each time one of them changes you would have to redo the calculation and
restore the result. Usually, the only reason to store a calculated value is
for historical purposes, but even then you can usually just store the values
that calculation is based on and, if necessary, the equation itself
(assuming it changes and you need to keep track of what equation you used
when).