Calculation on hidden amounts on a combo box

munkeyroot · Jun 15, 2018

Hi Everyone

hope you can help i am a little stuck on a project
i have a text box called percent - i don't want to use the amount as a customer may see this so i have a table with, and created a query for it.
Grade | Percent
A = 10
B = 15
C = 20
and made the Percent text box a combo box. and set the row source to the query.

on another text box i have the below code that i need to multiply the percent by.
=Nz(([CostofInstallation]*[Percent]),0)

how do i the code to choose the percent amount from the grade i enter.

hope this make sense

cheers
Munk

isladogs · Jun 15, 2018

Assuming that percent is the bound column, you could use Forms!FormName.Comboboxname.
Or set a variable/tempvar equal to the combo value then reference that

The_Doc_Man · Jun 15, 2018

Adding to Colin's comment, which focused on the bound column...

Forms!FormName.Comboboxname.Column is the content of the nth column if it happens that the hidden value isn't also the bound column. You CAN hide the bound column in a combo and it still works just fine.

MajP · Jun 15, 2018

Code:

is the content of the nth column

Actually would say the (n-1)th column since this is a zero based index. The first column would have an index of 0.

isladogs · Jun 15, 2018

Unless my memory is playing tricks, you can't refer to .Column(1) etc in query criteria.
So if you do need anything other than the bound column in query criteria, you would need to set a variable or tempvar equal to that value

Cronk · Jun 15, 2018

you would need to set a variable or tempvar equal to that value

...or a function returning the value of the required column.

Colin, I think the OP has the query as the row source of the combo. The required percentage column is being used for the text box

ie me.textbox =Nz(([CostofInstallation] * me.combo.column(1)),0)

The_Doc_Man · Jun 15, 2018

True that, MajP, on the fact that column is zero-based. Most things in Access ARE 0 to n-1, but some are not. Sometimes I forget which. Then I try it, find it doesn't work, and smack my forehead in a true V8 moment.

isladogs · Jun 15, 2018

Cronk said:
..or a function returning the value of the required column.

Agreed - in fact I regularly use a function for that purpose - never use tempvars myself

Cronk said:
Colin, I think the OP has the query as the row source of the combo. The required percentage column is being used for the text box

ie me.textbox =Nz(([CostofInstallation] * me.combo.column(1)),0)

Yes I saw that but picked up on the previous sentence

munkeyroot said:
and made the Percent text box a combo box. and set the row source to the query.

Perhaps I misread what that meant but that was the reason I referred solely to the bound column to try & avoid any issue with references to Column(1) etc

Here, as is so often the case, several replies can occur before the OP returns to clarify meanings

munkeyroot · Jun 18, 2018

Hi guys apologies i have just got back to you all thank you soo much for your input. i have attached a screenshot hope this will help.

yes so on the Revenue from Rental per annum Text box "TYRRetalRev_3YC".
i am using the control source to populate the maths from the combo box "TYRPercentage_PA" depending on which once is selected.

i hope this helps

cheers again munk

isladogs · Jun 18, 2018

Screenshot isn't that helpful IMO
Suggest you read through & then try out previous suggestions & identify which is appropriate for your situation

munkeyroot · Jun 18, 2018

Ahhh i think i have cracked it...well maybe a bit clunky but it works
i have my qry set on Grade | Percent. so because the 1st column is Grade as in A B C etc the code in the control source was picking up the letter .....so i switched them in the qry to be Percent | Grade so the C.Box shows the number. .. on the ComboBox made it a 2 column, hidden the Percent column by setting to 0cm and the grade to 2cm.

so thoughts guys?

cheers munk

isladogs · Jun 18, 2018

Or you could leave the combobox order as it was originally & set the bound column to column 2 (Percent).
Both methods should work as long as the values are unique

gemma-the-husky · Jun 21, 2018

Just one further observation. Combo box values are always strings, and you may need to use a conversion routine to change a text value into a numeric value. Occasionally I have found that I had to specifically change a text value into a numeric, although I am pretty sure that that sometimes it's automatic also. If you appear to get an issue with the data, you may need to do some conversion.

Calculation on hidden amounts on a combo box

munkeyroot

Registered User.

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

MajP

You've got your good things, and you've got mine.

isladogs

MVP / VIP

Cronk

Registered User.

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

munkeyroot

Registered User.

Attachments

isladogs

MVP / VIP

munkeyroot

Registered User.

isladogs

MVP / VIP

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread