Calculation in Qry

[tRIXY]

Hello all,

I have a qry with the following fields

Ingredients
Sold
Free
Employee
Manager
No Units

I export the results into excel and i have created a column to automatically caluculate the results i am looking for in Column J

Sold (Column e in xl)
Free (Column f
Employee (Column g)
Manager (Column h )
No Units (Column I)

The formula i have in column j to calculate the total no of units is as follows

=(IF(E9=I9,I9*-1,E9-F9-G9-H9-19)

cAN ANYONE PLEASE TELL ME HOW TO PERFORM THIS CALCULATION WITHIN MY ACCESS QRY?

tHANK YOU

 

[KenHigg]

See the IIF() function (Immediate If)

kh

 

[tRIXY]

Hi Ken,

Thanks for your response, i am fairy new to access myself, could you elaborate a litlle more for me on how to build the qry using the function you suggested

Rgds

T

 

[KenHigg]

Sure,

Do a new col in your query and instead of pulling a fld from your table, place in it, something simular to the following:

Total_Units: iif([Sold] = [No Units],[No Units]*-1,[Sold]-[Free]-[Employee]-[Manager]-19)


???
kh

 

[tRIXY]

thanks ken i wil give it a go

 

[tRIXY]

Hi Ken,

The first part of the calculation is working fine.Total_Units:iff([Sold] = [NoUnits],[NoUnits]*-1 but if that criteria is not met ,[sold]-[free]-employee]-mamager] is not calculating.The results in the Total_Units is displayiong the same figure from the Sold Field?

A little lost!

 

[KenHigg]

To start with I have no idea what are contained in the fields:

Sold
Free
Employee
Manager

or what relevence -19 has in your formula.

But they must be of a numberic data type and must have a '0' or a number, no nulls. Double check you names and the values your test data has for nulls.

Failing to find the problem with these suggestions, cut and past the entire iif() statement or post a sample db containing enough stuff to duplicate your problem...

???
ken

 

[tRIXY]

Ken,

My apologies but my formula in xl is actually -I9 NOT 19 (sorry)

So it should go as follows

=IF(e9=i9,i9*-1,e9-f9-g9-h9-i9)

Your suggested way of doing it in access is great it calculates if (e9=i9,i9*-1 but it is not performing the rest of the calculation.