Update Queries

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
 
Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob
 
thank you -- is there any chance you could show me how I would do that?

Jake said:
Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

Heather said:
Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
 
Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


Heather said:
thank you -- is there any chance you could show me how I would do that?

Jake said:
Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

Heather said:
Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

KARL DEWEY said:
Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


Heather said:
thank you -- is there any chance you could show me how I would do that?

Jake said:
Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
Open your query in design view and paste these in the Field row of the grid.
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, 0, ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, 0, ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, 0, (12* [Total
Fees] / [Mos] / FRP)

The above returns 0 (zero) if there is a divide by zero error.
If you want a zero lenght string then use these --
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, "", ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, "", ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, "",(12* [Total
Fees] / [Mos] / FRP)
--
Build a little, test a little.


Heather said:
oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

KARL DEWEY said:
Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


Heather said:
thank you -- is there any chance you could show me how I would do that?

:

Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
thank you so much, this is really helpful! :)

KARL DEWEY said:
Open your query in design view and paste these in the Field row of the grid.
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, 0, ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, 0, ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, 0, (12* [Total
Fees] / [Mos] / FRP)

The above returns 0 (zero) if there is a divide by zero error.
If you want a zero lenght string then use these --
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, "", ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, "", ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, "",(12* [Total
Fees] / [Mos] / FRP)
--
Build a little, test a little.


Heather said:
oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

KARL DEWEY said:
Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


:

thank you -- is there any chance you could show me how I would do that?

:

Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
Hi Karl,

I tried the discount formula today and its returning 100% for all of my rows
and it shouldn't be - here's the formula I used IIf([DATA]![List Price]=0 Or
[DATA]![Total Fees]=0,"",([DATA]![List Price]-[DATA]![Total
Fees])/[DATA]![List Price])

and here's examples of the data
List Price Total Fees
362,736 109,352 should return 70%
271,037 76,476 should retun 22%

any ideas?

Thank you for your help :)

KARL DEWEY said:
Open your query in design view and paste these in the Field row of the grid.
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, 0, ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, 0, ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, 0, (12* [Total
Fees] / [Mos] / FRP)

The above returns 0 (zero) if there is a divide by zero error.
If you want a zero lenght string then use these --
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, "", ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, "", ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, "",(12* [Total
Fees] / [Mos] / FRP)
--
Build a little, test a little.


Heather said:
oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

KARL DEWEY said:
Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


:

thank you -- is there any chance you could show me how I would do that?

:

Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
I'm wondering if it has anything to do with it being a percentage that I'm
not able to get the Discount or the Access Rate?

KARL DEWEY said:
Open your query in design view and paste these in the Field row of the grid.
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, 0, ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, 0, ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, 0, (12* [Total
Fees] / [Mos] / FRP)

The above returns 0 (zero) if there is a divide by zero error.
If you want a zero lenght string then use these --
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, "", ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, "", ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, "",(12* [Total
Fees] / [Mos] / FRP)
--
Build a little, test a little.


Heather said:
oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

KARL DEWEY said:
Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


:

thank you -- is there any chance you could show me how I would do that?

:

Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
I do not know how you came up with your formula for discount. I am used to
have a List Price, Sale Price, and then compute persent discount.

I did the math using your formula I get 70 and 71.8, not 70 and 22 as you
said.


--
Build a little, test a little.


Heather said:
Hi Karl,

I tried the discount formula today and its returning 100% for all of my rows
and it shouldn't be - here's the formula I used IIf([DATA]![List Price]=0 Or
[DATA]![Total Fees]=0,"",([DATA]![List Price]-[DATA]![Total
Fees])/[DATA]![List Price])

and here's examples of the data
List Price Total Fees
362,736 109,352 should return 70%
271,037 76,476 should retun 22%

any ideas?

Thank you for your help :)

KARL DEWEY said:
Open your query in design view and paste these in the Field row of the grid.
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, 0, ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, 0, ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, 0, (12* [Total
Fees] / [Mos] / FRP)

The above returns 0 (zero) if there is a divide by zero error.
If you want a zero lenght string then use these --
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, "", ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, "", ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, "",(12* [Total
Fees] / [Mos] / FRP)
--
Build a little, test a little.


Heather said:
oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

:

Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


:

thank you -- is there any chance you could show me how I would do that?

:

Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
your right I accidently typed 22 not 72 .. but when I do this in Access with
the formula
IIf([DATA]![List Price]=0 Or
[DATA]![Total Fees]=0,"",([DATA]![List Price]-[DATA]![Total
Fees])/[DATA]![List Price])

It's returning all 100%

KARL DEWEY said:
I do not know how you came up with your formula for discount. I am used to
have a List Price, Sale Price, and then compute persent discount.

I did the math using your formula I get 70 and 71.8, not 70 and 22 as you
said.


--
Build a little, test a little.


Heather said:
Hi Karl,

I tried the discount formula today and its returning 100% for all of my rows
and it shouldn't be - here's the formula I used IIf([DATA]![List Price]=0 Or
[DATA]![Total Fees]=0,"",([DATA]![List Price]-[DATA]![Total
Fees])/[DATA]![List Price])

and here's examples of the data
List Price Total Fees
362,736 109,352 should return 70%
271,037 76,476 should retun 22%

any ideas?

Thank you for your help :)

KARL DEWEY said:
Open your query in design view and paste these in the Field row of the grid.
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, 0, ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, 0, ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, 0, (12* [Total
Fees] / [Mos] / FRP)

The above returns 0 (zero) if there is a divide by zero error.
If you want a zero lenght string then use these --
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, "", ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, "", ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, "",(12* [Total
Fees] / [Mos] / FRP)
--
Build a little, test a little.


:

oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

:

Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


:

thank you -- is there any chance you could show me how I would do that?

:

Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your help, it turns out it was in the properties of the table
setup that was messing it up -- You're help has been instrumental :)

KARL DEWEY said:
I do not know how you came up with your formula for discount. I am used to
have a List Price, Sale Price, and then compute persent discount.

I did the math using your formula I get 70 and 71.8, not 70 and 22 as you
said.


--
Build a little, test a little.


Heather said:
Hi Karl,

I tried the discount formula today and its returning 100% for all of my rows
and it shouldn't be - here's the formula I used IIf([DATA]![List Price]=0 Or
[DATA]![Total Fees]=0,"",([DATA]![List Price]-[DATA]![Total
Fees])/[DATA]![List Price])

and here's examples of the data
List Price Total Fees
362,736 109,352 should return 70%
271,037 76,476 should retun 22%

any ideas?

Thank you for your help :)

KARL DEWEY said:
Open your query in design view and paste these in the Field row of the grid.
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, 0, ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, 0, ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, 0, (12* [Total
Fees] / [Mos] / FRP)

The above returns 0 (zero) if there is a divide by zero error.
If you want a zero lenght string then use these --
Annnual Fees: IIF([Total Fees]= 0 OR [Mos]= 0, "", ([Total Fees] / [Mos])*12)
Discount: IIF([List Price]= 0 OR [Total Fees]= 0, "", ([List Price]-[Total
Fees])/[List Price]
Access Rate: IIF(FRP]= 0 OR [Total Fees]= 0 OR [Mos]= 0, "",(12* [Total
Fees] / [Mos] / FRP)
--
Build a little, test a little.


:

oops sorry - I put the names in to demonstrate what it was in Excel so you
weren't just seeing the excel formula - so here it T329 = Total Fees and
X329= Mos

:

Syntax for IIF ---
IIF(Test, Results for True, Results for False)

But I do not understand the data you posted.
How does fields [Total Fees] and [Mos] relate to T329 and X329?

--
Build a little, test a little.


:

thank you -- is there any chance you could show me how I would do that?

:

Heather,

The biggest thing is that the Access equivilant of the Excel "IF" function
is called "IIF". This is because in Access IF is used for scripting. IIF
(Immediate If) is used to eveluate expressions.

That might be all you need to do.

Jacob

:

Is there a way to replicate this funtionality in Access -- these are formulas
I've been using in Excel

Annnual Fees: ([Total Fees] / [Mos])*12 =IF(T329=0, "", 12*X329/T329)
Discount: ([List Price]-[Total Fees])/[List Price] =(W329-X329)/W329
Access Rate: (12* [Total Fees] / [Mos] / FRP) =IF(ISERROR(12*X2/T2/V2), "",
12*X2/T2/V2)

Thank you,
Heather
Click to expand...
Click to expand...
Click to expand...
 
Back
Top