I need help writing this up in an access formula. This is going in to a Query.

Here it is in excel formula

 =IF(D11<6,Q11,0)



This is what I have in access.

mjm: IIf(([Transaction Errors]>=10,([Transaction Errors],0)



What ever is put in to [transaction Errors]> or = 10 then [Transaction Errors] is 0….(This is what I want)

Do you want an Update query to change the data from >=10 to 0, or do you want to show 0 where >=10 in query results?

Also, if this is not a permanent update, is this being presented in a report, or just in a query results datasheet? If it is in a report, you have other options.


mmcdonal

Look it up at: http://wrox.books24x7.com

I want to show 0 where >=10 in query results. This a permanent update…….

UPDATE MyTable
SET MyTable.TransactionErrors = 0
WHERE MyTable.TransactionErrors >= 10


This will be a permanent change to the column on which it is applied, so the values will all be reset to 0 and the previous values will be lost. Is that what you want?

This will not present any data, just change the underlying data in the table.




mmcdonal

Look it up at: http://wrox.books24x7.com

You're OK, you just have too many parentheses.

mjm: IIf([Transaction Errors]>=10,[Transaction Errors],0)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division