using .column as a number

[AceBK]

Hello,

I am trying to create a database for my business. In one table I have the different items that I sell. In this table I have fields available to put in different quantities that equal different unit prices, aka bulk discounts.

I have named the fields "PriceBreakQty1, PriceBreakQty2, etc up to PriceBreakQty8". I also have 8 fields that have the different unit prices. "PriceBreak1, PriceBreak2, ... PriceBreak8". After filling out all this information, I now go to my "Ordered" table where I choose the different items that the customers buy. I have built a combo box in my "Ordered" form with 16 additional columns, 1 for each of the price break qty's and there associated unit prices. As such I also put in an "After Update" of the quantity field, a module to search the columns of the combo box to find where the quantity fits in relation to the price breaks. I think the problem I am having is that the column information is reading as text and not as numbers so when I put in:
If Quantity <= PartNumber.Column(10) Then
Unitprice = PartNumber.Column(2)

It reads "PartNumber.Column(10) as text even though it is a number. Since Quantity is a number and Partnumber.Column(10) is text, it does not work. I think this because when I troubleshoot the code it says that "Quantity = 5" but "Partnumber.column(10) = "5". There are quotation marks around the number. The question is, how do I convert Partnumber.Column(10) to a number? Below is my complete code for what I am trying to do.

Private Sub Quantity_AfterUpdate()

If Quantity <= PartNumber.Column(10) Then
Unitprice = PartNumber.Column(2)
ElseIf Quantity <= PartNumber.Column(11) Then
Unitprice = PartNumber.Column(3)
ElseIf Quantity <= PartNumber.Column(12) Then
Unitprice = PartNumber.Column(4)
ElseIf Quantity <= PartNumber.Column(13) Then
Unitprice = PartNumber.Column(5)
ElseIf Quantity <= PartNumber.Column(14) Then
Unitprice = PartNumber.Column(6)
ElseIf Quantity <= PartNumber.Column(15) Then
Unitprice = PartNumber.Column(7)
ElseIf Quantity <= PartNumber.Column(16) Then
Unitprice = PartNumber.Column(8)
ElseIf Quantity <= PartNumber.Column(17) Then
Unitprice = PartNumber.Column(9)
End If
End Sub

 

[pbaldy]

I don't think I like the table design (not normalized), but try wrapping the reference in CInt() or CLng().

 

[Minty]

I really think you need to rethink your table structure. What happens when you want another price break level? You'll have to rebuild this completely.

I'm sure there are much better ways of handling this, you are handling it like a spreadsheet.

Look at calculating your price breaks rather than storing them all...