Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-08-2019, 06:44 AM   #1
BJF
Newly Registered User
 
Join Date: Feb 2010
Posts: 89
Thanks: 26
Thanked 0 Times in 0 Posts
BJF is on a distinguished road
If then statement in a query field

Hi,

Up to this point i have used more VBA then exploring the power of queries,
but I could really use some help with something that I think is possible but can not figure out.

If I have a query that has 2 fields, and i want to make one field = 0 if the other field is true, how do i go about writing that expression?

Would someone please give me a simple example (and syntax) that i can apply to my database based on what i have explained?

Thanks for your help,
Brian

BJF is offline   Reply With Quote
Old 05-08-2019, 06:52 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: If then statement in a query field

The syntax in the query designer is something along the line of;

Code:
MyCalcField: IIf([FieldA] = True, 0 , 99)
Where 0 is the true result and 99 is the false result.
You could replace 99 with another field if it contained the false value you wanted;


Code:
MyCalcField: IIf([FieldA] = True, 0 , [FieldB])
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
BJF (05-08-2019)
Old 05-08-2019, 06:54 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,695
Thanks: 57
Thanked 1,257 Times in 1,238 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: If then statement in a query field

Hi Brian. If the other field is not True (False), what do you want the new column to say? You could try the IIf() function. For example:
Code:
IIf([FieldName]=True,0,Null)

Edit: Oops, too slow...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
BJF (05-08-2019)
Old 05-08-2019, 06:59 AM   #4
BJF
Newly Registered User
 
Join Date: Feb 2010
Posts: 89
Thanks: 26
Thanked 0 Times in 0 Posts
BJF is on a distinguished road
Re: If then statement in a query field

I am looking to have a total field (which contains an amount in every record) = whatever amount each record has.....unless another field in the query is populated...and if that is the case, then i want my total field to equal 0.
BJF is offline   Reply With Quote
Old 05-08-2019, 07:01 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,695
Thanks: 57
Thanked 1,257 Times in 1,238 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: If then statement in a query field

Quote:
Originally Posted by BJF View Post
I am looking to have a total field (which contains an amount in every record) = whatever amount each record has.....unless another field in the query is populated...and if that is the case, then i want my total field to equal 0.
Sounds like the second option Minty posted is what you want then. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
BJF (05-08-2019)
Old 05-08-2019, 07:39 AM   #6
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,032
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: If then statement in a query field

Quote:
Originally Posted by BJF View Post
I am looking to have a total field (which contains an amount in every record) = whatever amount each record has.....unless another field in the query is populated...and if that is the case, then i want my total field to equal 0.
As an alternative, have a criteria on your query the omits all records where your "Other field" is not blank.

This means that, if only 4 records out of 254669 have the other field blank, you only return 4 records to total not all of them.

Mark_ is offline   Reply With Quote
Reply

Tags
criteria , expression , if then , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you make a field required perameters of another field (Possibly by iff statement) EmergentGuru Forms 3 09-16-2016 07:01 AM
IIF Statement with Yes/No Field Valery Modules & VBA 8 11-29-2013 10:49 AM
field if statement awake2424 Forms 1 05-11-2013 07:23 AM
Can I use an IF statement to create a value for a field in a Query? David Ball Queries 2 02-28-2011 05:12 AM
Query Delete Statement - Date field problem ctkmak Queries 1 10-27-2005 01:01 AM




All times are GMT -8. The time now is 02:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World