Can I use a where clause in a Control Source

[HockeyFan]

I have a form where I have a drop down combo box that has 3 values, 1, 2, and 3. (field A)

I have another field that contains numeric data.(field B)

I also have a third field(field C).

I would like the control source of field C to display the contents of the field B if field A=1.

In other words, I don't know if you can use a where clause in the control source of field C to say something like where [A] = "1" or something?

 

[jrbarnett]

See faq702-4289

John

 

[HockeyFan]

Thanks for the reply! Although this is similar, It is not exactly what I need. The reason why is because I have to do a bunch of math calculations and totals right on the form itself. So what I wanted to do was to be able to quickly use the value of field B if the value of field A was 1. I will also need to use the value of B if field A=2 and also if A=3.

 

[razchip]

You can use an iff statement. Iff([field A]=1,"B",else)

Thanks for the help.
Greg

 

[HockeyFan]

I think this is right along the lines of what I am looking for, however it's not working. I modified what you wrote a bit because in your example, B is also a field, so I put it into brackets. Iff([field A]=1,,else)

The problem is, that it still isn't displaying the value of field B, even when the value of field A is 1. Am I doing something wrong here?

 

[randy700]

I would like the control source of field C to display the contents of the field B if field A=1.

I will also need to use the value of B if field A=2 and also if A=3.

Since your original post states there are only 3 possible values for A, I see no need for a WHERE clause.

Control source for C...

=[field B]

Randy

 

[HockeyFan]

Thanks for the reply randy. The only problem with that, is the value that will be displayed in field C, will depend on what is the current value selected in the dropdown box of field A.

Once I figure out a method for doing this, I will be modifying this to do a lot of different calculations with my data. I'm just trying to find the best syntax to accomplish this.

I like the Iff method, but it's just not working for me yet.

 

[HockeyFan]

I got it. The problem was because I haven't used this statement in quite a while, and I wrote it just as razchip suggested, and I didn't realize that there was a typo in his suggestion.
It should have been IIf([field A]=1,,else).

Thank you everyone for your help. this website is the best.

 

[HockeyFan]

Ok, one more thing....

Is it possible to take this result, and do a sum of field B, only when field A has the value of 1?

=IIf([A]="1",Sum(),"")

The problem with my statement above, is that it is doing a sum of all of the records of field B, whereas I only want it to sum the records that have a 1 as a value for field A.

 

[jrbarnett]

Look at the DSum function.

John