NPV

[Mark]

I have a query and I want to use NPV function. The
Expression Builder I use the following statement:

Exp1: NPV(5,[Total Costs]![Incremental Benefits])

5% is the discount rate
The field [Incremental Benefits] is where each year's
value is

Why does access say the function is not defined in the
expression?

Thanks
Mark

 

[Van T. Dinh]

I think NPV is an Excel function and not an Access function.

If you want to use the Excel NPV, create an Excel object and then use the
NPV function via this Excel object.

 

[M.L. Sco Scofield]

Van,

I have to respectfully disagree.

NPV is actually in VBA. Unfortunately, it is only available through VBA, not
in Access objects like queries.

The article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;268159 describes how
to make a "wrapper" function so that it can be used in Access objects.

Mark,

You have a couple of problems. One easy. One not-so-easy.

1 - Help states that the first argument is a decimal. What they mean is that
5% must be entered as .05, not 5. (The easy one.)

2 - The second argument is an array. (The not-so-easy one.)

From your sample, I get the impression that all of the values are in
separate records and you are somehow expecting the query to magically
assemble them for you. If this is the case, you're not going to be able to
use this function in a query. Queries work row-by-row. It's going to require
some more code to read the values from separate records and then assemble
them into an array.

If all of your values were in separate fields in the same record, (breaking
all of the normalization rules,) you could use this function by simply
specifying each of the fields as a separate argument.

There might be a way to pull this off by using a crosstab query to flatten
the records and then put this function into another query based on the
crosstab query. This just might be more work than writing some more code.

Good luck.

Sco

I think NPV is an Excel function and not an Access function.

If you want to use the Excel NPV, create an Excel object and then use the
NPV function via this Excel object.

--
HTH
Van T. Dinh
MVP (Access)

I have a query and I want to use NPV function. The
Expression Builder I use the following statement:

Exp1: NPV(5,[Total Costs]![Incremental Benefits])

5% is the discount rate
The field [Incremental Benefits] is where each year's
value is

Why does access say the function is not defined in the
expression?

Thanks
Mark

 

[Van T. Dinh]

You are right. I checked it in the ObjectBrowser before I posted but I
must've made a typing mistake because nothing was returned when I searched
for it. I check it again after your post and found it this time.

 

[M.L. Sco Scofield]

Van,

Even if you found it the first time, the object browser wouldn't have lead
you to believe it wasn't usable in an Access object without a wrapper.

Arrrg...

Sco

 

[JMG]

UGH

What a load of work for something that is VERY simple in excel.

Let me play with it and I night be coming back with more questions

thanks
Mark

Van,

I have to respectfully disagree.

NPV is actually in VBA. Unfortunately, it is only available through VBA, not
in Access objects like queries.

The article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;268159 describes how
to make a "wrapper" function so that it can be used in Access objects.

Mark,

You have a couple of problems. One easy. One not-so-easy.

1 - Help states that the first argument is a decimal. What they mean is that
5% must be entered as .05, not 5. (The easy one.)

2 - The second argument is an array. (The not-so-easy one.)

From your sample, I get the impression that all of the values are in
separate records and you are somehow expecting the query to magically
assemble them for you. If this is the case, you're not going to be able to
use this function in a query. Queries work row-by-row. It's going to require
some more code to read the values from separate records and then assemble
them into an array.

If all of your values were in separate fields in the same record, (breaking
all of the normalization rules,) you could use this function by simply
specifying each of the fields as a separate argument.

There might be a way to pull this off by using a crosstab query to flatten
the records and then put this function into another query based on the
crosstab query. This just might be more work than writing some more code.

Good luck.

Sco

I think NPV is an Excel function and not an Access function.

If you want to use the Excel NPV, create an Excel object and then use the
NPV function via this Excel object.

--
HTH
Van T. Dinh
MVP (Access)

I have a query and I want to use NPV function. The
Expression Builder I use the following statement:

Exp1: NPV(5,[Total Costs]![Incremental Benefits])

5% is the discount rate
The field [Incremental Benefits] is where each year's
value is

Why does access say the function is not defined in the
expression?

Thanks
Mark

 

[M.L. Sco Scofield]

Mark,

Not so tough really. Once you know the hoops to jump through.

Since the beginning of time there has always been a price to pay when trying
to use one piece of software from another.

I can still remember writing an interface between EDL on an IBM Series/1 and
some obscure, COBOL like language that I don't even remember the name of
anymore. Now *that* was, as you say, "UGH."

Once I found the KB article, I barely had to do anything but copy and paste
the code from the article and it magically worked.

Your two problems are:

1 - The 5% return rate must be entered as .05, not 5. (From reading Excel
help, it's the same there.)

2 - In Excel, you can specify a range of cells for the Values argument. As
Access is not a spreadsheet program, there is nothing with a "range" object.
A query returns records row by row. There is no way to say to a query loop
through the records and give me a single result that looks like an Excel
range object. If all of your values are in different records, (as they
should be,) you have to make your own "range" using some VBA code to loop
through the records and load an array variable.

#1 is just a minor interpretation error.

#2 is a fundamental difference between spreadsheets and databases. This is
not Access's fault. It's a database.

Mar, if you continue to have trouble, let me know. If you beg nicely, next
week between jobs I'll throw some code together to show you what you need to
do.

Sco

UGH

What a load of work for something that is VERY simple in excel.

Let me play with it and I night be coming back with more questions

thanks
Mark

Van,

I have to respectfully disagree.

NPV is actually in VBA. Unfortunately, it is only available through VBA, not
in Access objects like queries.

The article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;268159 describes how
to make a "wrapper" function so that it can be used in Access objects.

Mark,

You have a couple of problems. One easy. One not-so-easy.

1 - Help states that the first argument is a decimal. What they mean is that
5% must be entered as .05, not 5. (The easy one.)

2 - The second argument is an array. (The not-so-easy one.)

From your sample, I get the impression that all of the values are in
separate records and you are somehow expecting the query to magically
assemble them for you. If this is the case, you're not going to be able to
use this function in a query. Queries work row-by-row. It's going to require
some more code to read the values from separate records and then assemble
them into an array.

If all of your values were in separate fields in the same record, (breaking
all of the normalization rules,) you could use this function by simply
specifying each of the fields as a separate argument.

There might be a way to pull this off by using a crosstab query to flatten
the records and then put this function into another query based on the
crosstab query. This just might be more work than writing some more code.

Good luck.

Sco

I think NPV is an Excel function and not an Access function.

If you want to use the Excel NPV, create an Excel object and then use the
NPV function via this Excel object.

--
HTH
Van T. Dinh
MVP (Access)



I have a query and I want to use NPV function. The
Expression Builder I use the following statement:

Exp1: NPV(5,[Total Costs]![Incremental Benefits])

5% is the discount rate
The field [Incremental Benefits] is where each year's
value is

Why does access say the function is not defined in the
expression?

Thanks
Mark