Help with Query

[ddmtn546]

Being a novice, I am trying to use a query to calculate when ColumnA & ColumnB are both true:

Rec ColumnA ColumnB ColumnC (result)
1 0 0 0 (if A=1 & B=1 then C=1)
2 1 0 0 (if A=1 & B=1 then C=1)
3 0 1 0 (if A=1 & B=1 then C=1)
4 1 1 1 (if A=1 & B=1 then C=1)

I tried, in the query:
count_ColC=true: IIf([ColumnA] & [ColumnB]=1,1,0)

Am I on the right track or is there an easier method. Thanks in advance.

 

[Quehay]

The procedural approach (if ___ then... )won't work in SQL (except PL-SQL in Oracle). You can do it with a recordset but SQL is easier using the IIf function. With IIF you have to be sure that both the true and false possibilities can be evaluated without error (no div by zero, etc.)

[tt]IIf( test, Result if true, Result is false)[/tt]

This would work:
[tt]
ColA ColB ColC
0 -1 iif(abs([ColA] + [ColB]= 2,True,False)

[/tt]
For the number of rows with both true: [tt] SUM(abs([ColC]))[/tt]

You could also skip the 'abs' function in the row and just have: (iif[ColA] + [ColB] = -2,True,False)

 

[RickSpr]

ddmtn546, your query expression shows that you don't have a grasp of of "expressions" in the programming sense. The English "If ColumnA and ColumnB are both equal to 1" does not translate as "IIf([ColumnA] & [ColumnB] = 1...)".
The correct translation is "IIf(([ColumnA] = 1) And ([ColumnB] = 1),...).

I'm not sure what you meant to do with the "count_ColC=true:" part in your query. If this is what you were putting in the Field row, you'd want this to be simply "count_ColC:", with the IIf expression following it.
Rick Sprague