calculate new value in expression in query

[Waldo]

Hello dear people,

I'm kinda new to access, but wish to program a little something to let the value decrease of the inventory for our business once a year. Wonder how to set up the formula in the query. What I have:

[current value]=iif (IsNull(Year(Date())- [year of purchase]), [original value], (1-(Year(Date())- [year of purchase])/ [depreciation])* [original value])



(the year of purchase, depreciation and original value are all values already in our database)

Would love some help here

Waldo

[pbaldy]

In design view, the way to add a calculated field is:

DesiredName: YourCalculationHere

Make DesiredName whatever you want, it's just what the resulting field will be named.

[Waldo]

Thanks for your reply pbaldy but even with your suggestion it doesn't work

current value: (iif (IsNull(Year(Date())- [year of purchase]), [original value], (1-(Year(Date())- [year of purchase])/ [depreciation])* [original value]))

Where's my mistake? Do I spell out the if-else statement wrongly?

[alansidman]

Try this:


current value: iif (Year(Date())- [year of purchase]="", [original value], (1-Year(Date())- [year of purchase])/ ([depreciation]* [original value]))

[Waldo]

Thank you for the suggestion but Access keeps on protesting, this time because
"the expression contains an invalid numer of arguments' if I translate correctly...

How should my variables be determined? As text or numeric or valuta or it doesn't matter?

waldo

[pbaldy]

Might depreciation be Null? Can you post a sample db with some data to test with?

[Waldo]

I set the query to only select those items with an original value higher than zero, hope that is good enough.

A link to a slimmed-down-version: http://www.box.net/shared/87poz7h38ofthn621hpx
hope it's clear enough & hope you can help me out here

thanks in any case for looking at it!

[pbaldy]

Can you post a compacted/zipped copy here? I don't like to download from unknown sites.

[Waldo]

Didn't know you could upload here, in attachment.

Waldo

[pbaldy]

The year field isn't in there. This ran without error:

CurrentValue: (IIf(IsNull(Year(Date())-[Aankoopjaar]),[original value],(1-(Year(Date())-[Aankoopjaar])/[depreciation])*[original value]))

[Waldo]

I really appreciate your help but I'm ashamed to say this but it still doesn't work with me when I input it in the field of the query "qrycurrent value" it keeps saying 'Invalid Syntax - Operand w/o operator'. Could you maybe post the database online with the edit in it? Would really help me a lot...!

[pbaldy]

This returns values without error. I don't know if they're the correct values, but there is no error.

SELECT tblinventaris.Id, tblinventaris.Categorie, tblinventaris.[original value], tblinventaris.depreciation, tblinventaris.Aankoopjaar, tblinventaris.Beschrijving, (Year(Date())-[aankoopjaar]) AS verschiljaar, tblinventaris.Code, (IIf(IsNull(Year(Date())-[Aankoopjaar]),[original value],(1-(Year(Date())-[Aankoopjaar])/[depreciation])*[original value])) AS CurrentValue
FROM tblinventaris
WHERE (((tblinventaris.[original value])>"0"));