ComboBox Form Column(2) calculation issue

[NixonShaun]

Good morning everybody.
I am working on a fairly simple calculation concept that is giving me the dreaded #Type! error.

at present the two tables causing the issues are
ServiceCodes
Billing

ServicesCodes has 3 fields:
AutoID
ServiceCode
ServiceCodeAmt - longinteger, general number (contains the hourly rate for this service code)

billing has various client demographics and
a ComboBox called [InvoiceService1] and a text box with hours provided for the month called [InvServ1Hrs]

I select a service using ComboBox to lookup Service Codes i add the hours for this service code in the text box next to the ComboBox.

I then created an unbound text area called [InvSrv1HrsAmt]
On the control source i entered
=[InvServ1Hrs]*[InvoiceService1].Column(2)
Access changes the text to =[InvServ1Hrs]*[InvoiceService1].[Column](2)
and the result is #Type!

However, if i change the Column to (0) if will give me the answer of 56 which is correct as the service selected in the combo box is row 7 and the hours entered are 8 hours totaling 56.
Column 0 as noted above is an autoID field, a long integer, general number.

curious why it works on the first column but not the third.
any ideas?

 

[Ranman256]

you cannot use columns in a query calc.
you CAN make a text box (visible or not) and when the user picks the item in a combo,
fill the text box:

sub cboBox_Afterupdate() 
  txtBox = cboBox.Column(2)
end sub

then use the textbox in the query.

 

[MajP]

you cannot use columns in a query calc

As the OP shows this does work for a calculated control.
if it was not working you would get #name. You get type likely because one of those values is a string and you are doing math on a string. I can get #type if I pick a column that is not numeric.

 

[NixonShaun]

This is quite confusing.
it is a form pulling info from a couple of tables.
the calculated field works partially depending on Column number.

ServiceCode table
ID ||Service || Amount
7 ||Job Coaching || 30.00

on the Billing form
cboBox called [InvoiceService1] (it looks up ServiceCode data)
text box called [InvServ1Hrs]
using the form you select the cboBox ServiceCode and input the number of hours in the text box alongside it.
the third text area is unbound and called [CrpInvS1Amt]
it is on this third field that i am attempting the calculated control.

e.g. i select "Job coaching" from cboBox and insert 8 into [InvServ1Hrs] to suggest that the invoice is for 8 hours of job coaching.
i want the third box/text [CrpInvS1Amt] area to calculate the 8 hrs * 30.00 the hours rate for ServiceCode 7 job coaching.

to see if i can get any info in the text area [CrpInvS1Amt] i have just entered:
=[Forms]![Billing]![InvoiceService1].[Column](0)
it inserts ...7... which is correct as per the table above

=[Forms]![Billing]![InvoiceService1].[Column](1)
it inserts ...7... which is not the second Column

=[Forms]![Billing]![InvoiceService1].[Column](2)
it inserts ...Job Coaching... this is almost correct except it is the second Column not 3rd.

=[Forms]![Billing]![InvoiceService1].[Column](3)
blank field which makes sense as there is no 4th Column

=[Forms]![Billing]![InvoiceService1].[Column](0)*[InvS1Hrs]
it inserts ...56... which is correct as Column(0) the ID Column has Job Coaching as Row 7.

so i have tried two methods;
=[Forms]![Billing]![InvoiceService1].[Column](2)*[InvServ1Hrs]
&
=[InvServ1Hrs]*[InvoiceService1].[Column](2)

and i still cant get it to calculate based on the 30.00 that should be in ServiceCodes row 7 Column 3

 

[NixonShaun]

SOLVED:
Thanks MajP and Ranman for your insight and ideas.
the problem turned out to be with the cboBox. The sql did not include the third column but only the ID and the Service name.
By adding
SELECT ServiceCodes.ID, ServiceCodes.Service, ServiceCodes.ServiceAmount
FROM ServiceCodes;
to the row source
i am now able to select the text field on the form and have it calculate the invoice hours X the cboBox Service name and the column 3 hourly rate.