Update Queries

Heather · Aug 10, 2009

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

Jake · Aug 10, 2009

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 · Aug 10, 2009

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...

KARL DEWEY · Aug 11, 2009

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...

Heather · Aug 11, 2009

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...

KARL DEWEY · Aug 11, 2009

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...

Heather · Aug 11, 2009

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...

Heather · Aug 13, 2009

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...

Heather · Aug 13, 2009

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...

KARL DEWEY · Aug 13, 2009

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...

Heather · Aug 13, 2009

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...

Heather · Aug 14, 2009

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...

Query Calculation - #Error	0	Mar 21, 2007
Query Help Please	3	Oct 11, 2011
Query Help Please	0	Oct 11, 2011
Access Append Query Failure	1	Jul 26, 2009
Complex Query Problem	1	Dec 12, 2007
Help with Query for new user	4	Jul 16, 2008
create queries	7	Mar 2, 2009
Excel function calculations used in MS Access query	1	May 22, 2008

Update Queries

Heather

Jake

Heather

KARL DEWEY

Heather

KARL DEWEY

Heather

Heather

Heather

KARL DEWEY

Heather

Heather

Ask a Question

Similar Threads