Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 8th, 2008, 04:54 PM
Registered User
 
Join Date: Jul 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with formula

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)




 
Old July 9th, 2008, 06:47 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 9th, 2008, 09:18 AM
Registered User
 
Join Date: Jul 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 9th, 2008, 09:25 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old August 1st, 2008, 07:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with formula 2 Juan0214 Access 3 July 10th, 2008 06:52 PM
Formula scandalous Access VBA 11 February 27th, 2007 09:49 AM
formula sinha Crystal Reports 1 October 26th, 2005 12:53 PM
Help with a formula Corey Access 2 October 20th, 2005 10:48 AM
Formula Ned Pro VB 6 2 September 10th, 2003 10:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.