Calculation using Combobox output

[Evon]

What code can I use to multiply the quantity field by the cost field on a form, when the cost field is the output of a combobox.

Been trying lots of angles but no breakthrough.

 

[pbaldy]

In code?

Whatever = Me.QuantityTextbox * Me.ComboName.Column(x)

where x is the zero-based number of the column containing cost.

 

[Evon]

Thanks pbaldy, I was doing something wrong all along. I did not include all the fields in the select statement.
I am good now.

 

[pbaldy]

Glad you got it sorted.

 

[Evon]

Still not out of the woods yet. Did the calculation but can not get the result in currency format, even though I set the currency format in both the table and the form.

I actually used the =comboboxName.column to filter the unitprice from the combobox to a text field. Then multiplied that text field by another quantity text field to get a totalcost.

Every other currency field displays as currency except the unitprice

 

[pbaldy]

How about adding the CCur() function to your calculation?

 

[Evon]

You are the best. Knew there had to be a function!
Worked perfectly!1

 

[pbaldy]

Happy to help!

 

[Evon]

Almost out of the woods, now the blank fields show an #error when no data is present. Tried using CCur(0) as default value, but that doesn't work.
Any more ideas pbaldy?

 

[pbaldy]

Yeah, hike to the top of Dunn's River Falls and the cool water will soothe away the errors.

Try wrapping any field that could be null in the Nz() function.

 

[Evon]

Yes, was wondering about nz.
Wat you know about Dunns River Man?

 

[pbaldy]

My wife and I went to Jamaica on our honeymoon, many years ago. Went back one other time. Of course we went to the falls, and one of my favorite pictures of us was taken there.

 

[Evon]

Good to know you both enjoyed your stay here. We go that side of the island sometimes. And me myself have been here for many years, proly longer than you.
Anyway, I am still working my way out "of the woods"

 

[Evon]

How do I get each of these 2 fields to be "0" when they have no data:


UnitPrice =CCur([Combo672].[Column](3))

Total =[Text678]*[UnitPrice]


Tried the Nz function but nothing happens. Probly not using it right.

 

[pbaldy]

How did you use it?

 

[Evon]

Nz(=CCur([Combo672].[Column](3)) ),0)

or something like that. Am currently looking at the dlookup function to force a default value in the fields on my invoice. The #errors in the fields without data is causing me not to get a total at the foot of the invoice.

 

[pbaldy]

The function should be innermost:

=CCur(Nz([Combo672].[Column](3),0))

And in any case you'd always start with the equals sign.

 

[Evon]

me a tell yu boy, u a de best....viola! I am goin' to make you an honorary Jamaican, how bout that.?! Weekend for 2 at Dunns River for you and yours.

Anyway, judging from the way you go about things you definitely are very wealthy, so you probably don't need that offer.

 

[pbaldy]

Ah, that makes me feel irie, mon! I'll get the first round of Red Stripes. I wish I was wealthy; I'm just a working stiff married to another working stiff. But I'm very happy to be an honorary Jamaican!

 

[Evon]

Back again my friend. So I closed & reopened the database and the values in the Nz fields were not stored am now getting # name.