Why is this formula not working?

[sondriven]

I have a combo box and a text box. The combo box has 2 columns. I want to multiply one of the columns from the combo box and the number that is in the text box. Heres the formula Ive been trying to get to work and dont know why it isnt.

=([Food.Column(2)]*[Servings])

and tried

=[Food.Column(2)]*[Servings]

Thanks for any help you can provide.

j

 

[jfgambit]

J:

Assuming you have an addional textbox (let's call it txtTotal in this example) on your form, add the following to the AfterUpdate Evewnt of the serving textbox:

Me.txtTotal = Me.ComboboxName.Column(2) * Me.ServingTextboxName

HTH

Jeff

 

[R. Hicks]

If the combo row source contains 2 columns ... the first column on the left is Column(0) and the second would be Column(1).

So the expression should be:

=([Food.Column(1)]*[Servings])

HTH
RDH

 

[sondriven]

Thanks for the replies but neither way worked. I tried column numbers 0-3 and all came up with the #error or Name#

Are there any reasons why this would be?

Thanks.

j

 

[jjturner]

Did you try:

=[Food].[Column](1)*[Servings]

HTH,
John

 

[sondriven]

Just tried got the #Name? error.

I think im outta luck here.

Thanks for advice

j

 

[jjturner]

Presuming your number column is the 2nd column in your combo box, did you make sure that the BoundColumn property is set to '2' for it?

 

[jjturner]

Scratch Last Comment!

I just figured out that my test worked no matter what I used as the BoundColumn...

Afraid I'm out of ideas on this one

 

[Rich]

Are you sure your combo is called Food ?

 

[sondriven]

Sweeet! Got it!

The field that the data was being stored was called Food. The combo box itself was Combo14.

Thanks, would of never saw that before this forum.

J

 

[sondriven]

Now that Ive got the formula to work in the subform thanks to all of you, how can I get the formula to sum up in the footer of the subform?

Ive tried these 2 to no avail:

=Sum([Combo14].Column(2)*[Servings])

and

=Sum(Nz([Combo14].[Column](2)*[Servings],0))

I keep getting the pesky #error message in the box.

Thanks

 

[sondriven]

Please help....getting desperate....

 

[jjturner]

=Sum([Food]*[Servings])

Make sure that in the FormFooter Sum expression you're using the Field names and not the Control names.

Check out Access help 'about computing a total for a calculated control on a form or report' under the Sum function topic

Regards,
John