value to be lookup not primary ID (1 Viewer)

[JuniorWoodchuck24]

Row Source in Table (combo box)

IFF([Type] = 13, (SELECT ProductTypes.ProductID, ProductTypes.[Extended Price] FROM ProductTypes ORDER BY [Extended Price], (SELECT ProductTypes.ProductID, ProductTypes.Price FROM ProductTypes ORDER BY Price)

I can get a number to pull up but it's the primary key. Trying to get it to pull in the 2nd value (Price or Extended Price).

 

[JuniorWoodchuck24]

the smilies are actually semi colons not smilies

 

[highandwild]

You cannot set the Row Source like this.

IF [Type] is on the form then have a query upon which the combo box is based.

The query should have a calculated field that has a column heading of ThePrice for example and then an IIf statement that makes reference to the [Type] field text box and uses the [Extended Price] field if it is 13 and the [Price] field if it is no. The column heading cannot be Price as you have a field by this name.

Order the query by this field.

You will have to requery the combo box in the OnGotFocus event of the combo box.

 

[Galaxiom]

BTW: The Immediate If function is IIF not IFF.

 

[The_Doc_Man]

IFF([Type] = 13, (SELECT ProductTypes.ProductID, ProductTypes.[Extended Price] FROM ProductTypes ORDER BY [Extended Price], (SELECT ProductTypes.ProductID, ProductTypes.Price FROM ProductTypes ORDER BY Price)

In the actual rowsource you cannot do this. But you can use a query as a rowsource and the query could look something like THIS if [Type] is another field in the same record.

SELECT ProductTypes.ProductID as ProdID, ProductTypes.[Extended Price] as ProdPrice FROM ProductTypes WHERE ProductTypes.[TYPE]=13 ORDER BY [Extended Price]
UNION
SELECT ProductTypes.ProductID as ProdID, ProductTypes.Price as ProdPrice FROM ProductTypes WHERE ProductTypes.[TYPE] <> 13 ORDER BY Price;

Note that if the overall price ordering is important, built a separate union query without an orderby and make your rowsource query point to the union query and do the orderby on the result. I.e. a layered query - inner layer to do the lookups and outer layer to do the ordering, grouping, etc.

 

[highandwild]

Correct me if I am wrong but I thought that what was required was either
a list where the price was the [ExtendedPrice] or the [Price] dependant on the value of [Type].

Would this do the trick:

SELECT ProductTypes.ProductID AS ProdId, IIf([Type]=13,[ExtendedPrice],[Price]) AS ProdPrice FROM ProductTypes
ORDER BY IIf([Type]=13,[ExtendedPrice],[Price]);

 

[pkstormy]

Couldn't you just make this a query where Type=13 and save it (ie. TypeIs13QueryName). Then when appropriate, set the rowsource name of the combobox to this query name:

ie. me.MyComboboxName.rowsource = "TypeIs13QueryName"

If there's another situation, set the rowsource query to another query name (ie. TypeIsNot13QuerName) or something like that.

In one of the queries, you can have 1 calculation, in another query, you can have a different calculation. Just make sure the column count is the same and the correct column shows in the query for all queries.