In Access VBA expression builder, how do I sum a column conditionally?

Question

Let's say I have two fields A and B and one textbox B_input. I would like to set up a query so it sums all entries of column A where B = B_input

Currently I have:

==Sum(IIf([B_input]<>"All",[A],IIf([B_input]<>[B],0,[A])))

I did more testing, it seem the problem is that under Sum(IIF([B_input])), it's not recognizing the value of [B_input], but if I just have IIF([B_input]), it recognize the value just fine, any ideas?

We are trying to edit the post at the same time, I'll wait a while before trying to make more changes. — JimmyPena, Jan 05 '12 at 19:12

score 0 · Answer 1 · answered Jan 05 '12 at 19:16

0

iif([B_input]=="xyz",Sum[A],False)

Might be what you're after but I'm not sure i understood your question properly.

Alternatively, just edit the sql to something like

SELECT(SUM[A]) AS SumOfA FROM [MyTable] HAVING ("B"="xyz");

answered Jan 05 '12 at 19:16

nobody

269
2
3

score 0 · Answer 2 · answered Jan 05 '12 at 19:18

0

How about:

=Sum(IIf([B_input]<>"All",[A],0))

answered Jan 05 '12 at 19:18

Fionnuala

90,370
7
114
152

@Yongke Where are you trying to do this? Because that is a fairly standard answer. – Fionnuala Jan 05 '12 at 19:32
Expression Builder of a textbox in a forum. Does it have to do with [B_input] is a forum textfield and not a table field? – Bill Software Engineer Jan 05 '12 at 20:13
@Yongke So what are you summing? Do you mean a textbox in the form footer of a continuous form? Because if not, you need to say exactly what you wish to achieve - sum may not be the answer. – Fionnuala Jan 05 '12 at 20:16
Yes, that is the case. I have a textbox at the end of a continous form. [A] and [B] are columns, while [B_input] is a textbox. It doesn't seem to recognize the textbox value of [B_input]. – Bill Software Engineer Jan 05 '12 at 20:24
Ok I did more testing, this seem to be the culprit: =Sum(IIf([B_input],1,2)), it simply does not recognize the value of B_input, but if I have =IIf([B_input],1,2) then it does, any ideas? – Bill Software Engineer Jan 05 '12 at 20:28
I have just tested with the form footer of a continuous form `=Sum(IIf([AText]="a",[ANumber],0))` and it works as expected. What data type is B_Input? Unless it is YesNo, you need to have a equality there somewhere. If it is YesNo, it may be worth trying `=True` – Fionnuala Jan 05 '12 at 20:41
B_input is a combo box, but it's value are text. – Bill Software Engineer Jan 05 '12 at 20:44
I did a test with exactly what you wrote: =Sum(IIf([B_input]="All",1,2)), it throw the same error. Does combo box make any difference from textfield? – Bill Software Engineer Jan 05 '12 at 20:47
Are you sure the bound column is text? Just put `=[B_Input]` to test, then use the format in my comment above. – Fionnuala Jan 05 '12 at 20:47
If I put =[B_input], it get the value just fine. In fact, it get the value just fine even with =IIf([B_input],1,2), it's just when I wrap a SUM outside of the IIF the error occurs. – Bill Software Engineer Jan 05 '12 at 21:22
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/6445/discussion-between-remou-and-yongke-bill-yu) – Fionnuala Jan 05 '12 at 21:33
I gave up, I used form filter instead. – Bill Software Engineer Jan 05 '12 at 22:03

score 0 · Accepted Answer · answered Jan 05 '12 at 22:47

0

Gave up, went with form filtering instead.

answered Jan 05 '12 at 22:47

Bill Software Engineer

7,362
23
91
174

In Access VBA expression builder, how do I sum a column conditionally?

3 Answers3