How to refer to Column 2 in a Combo Box

[MSAccessRookie]

I will admit up front that I should know the answer to this, but here I am anyway.

I have a pricing structure that will vary according to several Combo Box choices. An Example of one of them is below. Note that a user only sees Column 1 as the column widths are set at 1.5";0". My question is regarding how to refer to the Price Change (Column 2 in the Combo Box)

[FONT=Courier New]   Combo Box Choice       Amount of Price Change[/FONT]
[FONT=Courier New]   "<=20%"                         0.00[/FONT]
[FONT=Courier New]   ">20 % and <= 45%"              3.00[/FONT]
[FONT=Courier New]   "> 45%"                         5.00[/FONT]

 

[boblarson]

In code:

Me.YourComboName.Column(1)

in queries, control sources:

= [Forms]![YourFormNameHere]![YourComboName].[Column](1)

 

[boblarson]

Combos use ZERO-BASED numbering for the columns (column 0 is the first column, column 1 is the secont column)

 

[MSAccessRookie]

Combos use ZERO-BASED numbering for the columns (column 0 is the first column, column 1 is the secont column)

Then the column would be referred to as [myCombo].[Colum1]?

 

[boblarson]

Then the column would be referred to as [myCombo].[Colum1]?

No, it would be:

[myCombo].[Column](1)

 

[pbaldy]

IIRC, you can't use the column property in a query criteria.

 

[MSAccessRookie]

Then the column would be referred to as [myCombo].[Colum1]?

This did not work. I appear to have left off an important point.

The company that I work for sells very specialized animal feed. I have an unbound form that presently contains something like:

=[500kgPricing]+15.00
​

The Company has decided that since it costs more to make Higher Fat content Feed, they want to charge more for it. I tried to add the Combo Box Results in the following manner:

=[500kgPricing]+15.00+[FatinDiet].[Column1]
​

Is there a way to refer to Column1 in this context?

 

[boblarson]

IIRC, you can't use the column property in a query criteria.

Yeah, I guess you're right. You can do it in a control source but not as criteria in a query (unless you built the query via VBA).

 

[boblarson]

Is there a way to refer to Column1 in this context?

Yes, if you look at how I did it, you are still not writing it correctly:

Look at the placement of the 1 in () and the word COLUMN in SQUARE brackets, not COLUMN1 together:

=[500kgPricing]+15.00+[FatinDiet].[Column](1)

 

[MSAccessRookie]

IIRC, you can't use the column property in a query criteria.

For what it is worth, I changed it to [FatinDiet].[Column](1), and it works with one exception. Since there is no default value for the Combo Box. the field remained blank until a choice was made.

That is easy enough to fix by getting approval for a default value, unless there is another option.

In the mean time, thanks for getting me straight.

 

[MSAccessRookie]

Yes, if you look at how I did it, you are still not writing it correctly:

Look at the placement of the 1 in () and the word COLUMN in SQUARE brackets, not COLUMN1 together:

=[500kgPricing]+15.00+[FatinDiet].[Column](1)

That was only because we were cross posting (you are just too fast for me). Thanks again.

 

[boblarson]

That was only because we were cross posting (you are just too fast for me).

Actually, if you look you'll see that's what I said in my very first response to you

 

[MSAccessRookie]

Actually, if you look you'll see that's what I said in my very first response to you

I did see that and reacted accordingly. I appreciate the assistance