Error: "The expression is typed incorrectly or it is too complex..." on a UNION (1 Viewer)

[gbrenkman]

Error: "The expression is typed incorrectly or it is too complex..." on a UNION

I have a query:

SELECT 'NA_List_Price' AS PriceList, prices.ModelNumber, IIf(prices.NA_List_Price is Null,-1,IIf(prices.NA_List_Price = "N/A",-2,IIF(Left(prices.NA_List_Price,4)="Call",-3,prices.NA_List_Price))) AS Price, [Select Start mm/dd/yyyy] AS StartDate, '' AS EndDate
FROM prices

UNION

SELECT 'NA_Disti_Price' AS PriceList, prices.ModelNumber, IIf(prices.NA_Disti_Price is Null,-1,IIf(prices.NA_Disti_Price = "N/A",-2,IIF(Left(prices.NA_Disti_Price,4)="Call",-3,prices.NA_Disti_Price))) AS Price, [Select Start mm/dd/yyyy] AS StartDate, '' AS EndDate
FROM prices

that gives the Expression is typed incorrectly or is too complex... error.

If the query has one or the other SELECT, it works fine.

If using the first SELECT, I get prompted for a date (enter 01/11/2012) and the results are correct:

PriceList ModelNumber Price StartDate EndDate
NA_List_Price 1-00664-01 650 01/11/2011
NA_List_Price 1-00827-01 125 01/11/2011
NA_List_Price 1-00827-02 150 01/11/2011

The second SELECT also works correctly returning:

PriceList ModelNumber Price StartDate EndDate
NA_Disti_Price 1-00664-01 455 01/11/2012
NA_Disti_Price 1-00827-01 88 01/11/2012
NA_Disti_Price 1-00827-02 105 01/11/2012

The source Prices table looks like this:

ModelNumber NA_List_Price NA_Disti_Price
1-00664-01 650 455
1-00827-01 125 88
1-00827-02 150 105

It's when I UNION the two to attempt to get a listing like this:

PriceList ModelNumber Price StartDate EndDate
NA_List_Price 1-00664-01 650 01/11/2011
NA_List_Price 1-00827-01 125 01/11/2011
NA_List_Price 1-00827-02 150 01/11/2011
NA_Disti_Price 1-00664-01 455 01/11/2012
NA_Disti_Price 1-00827-01 88 01/11/2012
NA_Disti_Price 1-00827-02 105 01/11/2012

that the error occurs.

A bit of background about the IIf conditions, if that helps. The price fields in the database are defined as Text, not Number fields because some prices may be null, "N/A" or "Call for quote". The system I want to import the listing to does not allow text values as prices, so it uses -1 for null, -2 for "N/A" and -3 any time the source price starts with "Call." So the IIf statements do that conversion, and if the source price is not null, not "N/A" or does not begin with "Call" then it uses the actual, real price value.



Why do the queries work separately but not when combined with a UNION?

Any ideas or alternative solutions to get the output I need will be appreciated. Thanks!

Gary

 

[WayneRyan]

Re: Error: "The expression is typed incorrectly or it is too complex..." on a UNION

g,

Are you sure that your IIF statements take into consideration ALL of the values for
NA_List_Price and NA_Dist_Price? I get the suspicion that there are more values
than "N/A" and "Call". If that is the case, you're probably not returning a NUMBER
which is what the UNION query mandates.


hth,
Wayne

 

[gbrenkman]

Re: Error: "The expression is typed incorrectly or it is too complex..." on a UNION

Ah hah!

You provided the clue. The price fields are defined as Text. Some prices are numbers (stored as text) wheres other prices are text values "N/A", "Call for quote" or null.

I wasn't aware that the UNION required numeric values coming out of the IIF evaluations.

So when I changed the IIF parts of the query to something like this:

IIf(prices.NA_List_Price is Null,-1,IIf(prices.NA_List_Price = "N/A",-2,IIF(Left(prices.NA_List_Price,4)="Call",-3,Val(prices.NA_List_Price))))

using the VAL function to convert the text price (that is not null and not "N/A" and not "Call for quote" to a number, it all works fine now.

Sure would be nicer if Access gave more of a clue as to what it's having a problem with!

THANK YOU SO VERY MUCH!

Gary

 

[WayneRyan]

Re: Error: "The expression is typed incorrectly or it is too complex..." on a UNION

Gary,

Thanks, btw the UNION query doesn't mandate that the IIf statements return
numbers. The queries must match up in datatypes; within the query and as
the product of both queries.

Glad you got it working.

Wayne