If then statement in a query field (1 Viewer)

BJF

Registered User.
Local time
Today, 06:05
Joined
Feb 19, 2010
Messages
133
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
 

Minty

AWF VIP
Local time
Today, 10:05
Joined
Jul 26, 2013
Messages
10,355
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])
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:05
Joined
Oct 29, 2018
Messages
21,358
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...
 

BJF

Registered User.
Local time
Today, 06:05
Joined
Feb 19, 2010
Messages
133
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:05
Joined
Oct 29, 2018
Messages
21,358
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!
 

Mark_

Longboard on the internet
Local time
Today, 03:05
Joined
Sep 12, 2017
Messages
2,111
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.
 

Users who are viewing this thread

Top Bottom