Adding Colums Error

[byujakedmb]

I've tried the following and none of them work..

1) LCHTotalInvestment: CCur((1.025*[ACPurchasePrice])+[WAI2]+[WAI3]+[WAI4])
2) [ACPurchasePrice])+[WAI2]+[WAI3]+[WAI4]

The values in the referenced columns are set as currency. What am I doing wrong?

 

[pbaldy]

What's the error? If any of the fields could be Null, you'd have to wrap them in the Nz() function.

 

[byujakedmb]

What's the error? If any of the fields could be Null, you'd have to wrap them in the Nz() function.

When I run the query the result is #ERROR. How do I use the Nz() function

 

[pbaldy]

I think it's pretty well documented in Help. Check it out there and post back if you still have questions.

 

[byujakedmb]

got it thanks!

I have a new problem now. The following formula returns the error "wrong number of arguments." Does that mean there is too much going on or does it mean I haven't structured the code right?

TotalAnnualizedReturnDollars: ccur(((1.025*(nz(ACpurchaseprice)))*(((((1.025*(nz(ACpurchaseprice)))/nz(lchtotalinvestment))*nz(netprofit))/(1.025*(nz(ACpurchaseprice))))*(365/(datediff(nz(purchasedate),nz(datesold))))))+((nz(WAI2))*(((((nz(WAI2))/nz(lchtotalinvestment))*nz(netprofit))/(nz(WAI2)))*(365/(datediff(nz(WAI2Date),nz(datesold))))))+((nz(WAI3))*(((((nz(WAI3))/nz(lchtotalinvestment))*nz(netprofit))/(nz(WAI3)))*(365/(datediff(nz(WAI3Date),nz(datesold))))))+((nz(WAI4))*(((((nz(WAI4))/nz(lchtotalinvestment))*nz(netprofit))/(nz(WAI4)))*(365/(datediff(nz(WAI4Date),nz(datesold)))))))

 

[pbaldy]

At a glance, the DateDiff functions are off. The first argument is the interval, which you've left out.

 

[byujakedmb]

Ok so I added the interval into the datediff functions and now i get a #error.

TotalAnnualizedReturnDollars: ccur(((1.025*(nz(ACpurchaseprice)))*(((((1.025*(nz(ACpurchaseprice)))/nz(lchtotalinvestment))*nz(netprofit))/(1.025*(nz(ACpurchaseprice))))*(365/(datediff("d",nz(purchasedate),nz(datesold))))))+((nz(WAI2))*(((((nz(WAI2))/nz(lchtotalinvestment))*nz(netprofit))/(nz(WAI2)))*(365/(datediff("d",nz(WAI2Date),nz(datesold))))))+((nz(WAI3))*(((((nz(WAI3))/nz(lchtotalinvestment))*nz(netprofit))/(nz(WAI3)))*(365/(datediff("d",nz(WAI3Date),nz(datesold))))))+((nz(WAI4))*(((((nz(WAI4))/nz(lchtotalinvestment))*nz(netprofit))/(nz(WAI4)))*(365/(datediff("d",nz(WAI4Date),nz(datesold)))))))

Anyone know whats up?

 

[vbaInet]

The code is beginning to become unreadable. You will be able to spot your problem if you convert it to a Public Function in a module and call that function from the textbox's Control Source.

 

[byujakedmb]

I figured out the problem. Thanks for your help! Also, sometimes i go to open up a query and a box pops up that asks me to enter parameter value for all of the attributes.. what is this and why does my query come up blank when I dont enter a parameter?

 

[vbaInet]

You may have renamed some field names or deleting some fields from the original table. Open up the query in DESIGN VIEW and check all the field names.

 

[byujakedmb]

The fields did not change, but after re-entering the same field names it worked. Do you know why that could be? Also, how do I get the calculated value to show as a percentage in a query?

 

[vbaInet]

A link was probably missing somewhere.

You can use the Format() function in your query. Have a look at this:

http://www.techonthenet.com/access/functions/numeric/format.php

However, I would be more inclined to do it on the Control that will display the value instead of the query.

 

[byujakedmb]

A link was probably missing somewhere.

You can use the Format() function in your query. Have a look at this:

http://www.techonthenet.com/access/functions/numeric/format.php

However, I would be more inclined to do it on the Control that will display the value instead of the query.

How do you do it the second way and what are the advantages of doing it that way as opposed to the first way?

 

[vbaInet]

There's no disadvantage. If that query is solely for the form or you ALWAYS want to represent that value as a percentage and nothing else within your db then do it on the query level.

 

[DCrake]

You also need to be thinking about using BODMAS Brackets Outside Multiplications, Additions & Subtractions. If you do not bracket you equasion correctly you will get some eird and wonderful answers.

 

[vbaInet]

Good point David.

I remember mentioning BODMAS to a poster once and they didn't get it