If then statement in a query field

[BJF]

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]

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

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;

MyCalcField: IIf([FieldA] = True, 0 , [FieldB])

 

[theDBguy]

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:

IIf([FieldName]=True,0,Null)

Edit: Oops, too slow...

 

[BJF]

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 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_]

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.