Data Type mismatch in a query that uses a calculated field from another query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John T Ingato - 12 Dec 2006 05:50 GMT

I have a query "Q2" that depends on data generated by query "Q1". Query SQL
is at bottom of message.

Q1 has a field that calculates the difference between two weeks of inventory
levels to produce a sales quantity figure for that period. If the current
week is greater the the previous week, then it is obvious that the account
received some product and a module function "CalculatedSales" is called to
factor out the received product to generate the sales figure.

I used an "iif" statement in the field to determine if the function needs
calling as follows:
AdjustedSalesThisWeek: IIf(NZ(a.onhand-b.onhand) < 0,
CalculatedSales(b.OnHand,a.OnHand,[Buy Pack]),NZ(a.onhand-b.onhand))

It works perfectly.

The problem is when the second query Q2 try to average the above field
"AdjustedSalesThis Week".
I get an error stating "Data Type Mismatch in Criteria Expression". Its as
if by using the if statement, I no longer have a numerical value.

Any suggestions?

Q2 SQL:
SELECT qryCalculateSalesHistory.StoreNumber,
qryCalculateSalesHistory.SKUNumber,
Avg(qryCalculateSalesHistory.AdjustedSalesThisWeek) AS
AvgOfAdjustedSalesThisWeek,
Avg(qryCalculateSalesHistory.AdjustedSalesThisWeek) AS
AvgOfAdjustedSalesThisWeek1
FROM qryCalculateSalesHistory
GROUP BY qryCalculateSalesHistory.StoreNumber,
qryCalculateSalesHistory.SKUNumber;

Q1 SQL:
SELECT a.StoreNumber, a.SKUNumber, tblProductLine.ItemNumber,
tblProductLine.[Buy Pack], a.ReportDate AS FromDate, b.ReportDate AS ToDate,
a.OnHand, b.OnHand, NZ(a.onhand-b.onhand) AS SalesThisWeek,
IIf(NZ(a.onhand-b.onhand) < 0,CalculatedSales(b.OnHand,a.OnHand,[Buy
Pack]),NZ(a.onhand-b.onhand))
AS AdjustedSalesThisWeek, IIf([salesThisWeek]<0,"PO Rcv'd","") AS [PO
Status]
FROM tblProductLine INNER JOIN ((tbl_HD_2006_852_POS_Data AS a INNER JOIN
tbl_HD_2006_852_POS_Data AS b ON (a.StoreNumber = b.StoreNumber) AND
(a.SKUNumber = b.SKUNumber)) INNER JOIN tblProgramHomeDepot ON b.SKUNumber =
tblProgramHomeDepot.SKU) ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber
WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])));

Reply to this Message

tina - 12 Dec 2006 06:25 GMT

what Data Type is the CalculatedSales() function returning? also, try
opening Q1 in datasheet view. is the data in the AdjustedSalesThisWeek field
left-aligned or right-aligned? text (string) values automatically align
left, numeric values align right. if the Q1 values in the calculated field
are Text data type, suggest you try to convert to the Number data type you
need, from within Q1. if it won't work there, try converting the field from
within Q2. choose the appropriate conversion function, such as CLng(),
CDbl(), etc.

hth

> I have a query "Q2" that depends on data generated by query "Q1". Query SQL
> is at bottom of message.
[quoted text clipped - 44 lines]
> tblProgramHomeDepot.ItemNumber
> WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])));

Reply to this Message

John T Ingato - 12 Dec 2006 14:23 GMT

You hit it on the nose. Q1 data was left aligned. The function was
returning a variant.

> what Data Type is the CalculatedSales() function returning? also, try
> opening Q1 in datasheet view. is the data in the AdjustedSalesThisWeek
[quoted text clipped - 66 lines]
>> tblProgramHomeDepot.ItemNumber
>> WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])));

Reply to this Message

tina - 13 Dec 2006 04:51 GMT

ah, glad that helped you figure it out. :)

> You hit it on the nose. Q1 data was left aligned. The function was
> returning a variant.
[quoted text clipped - 69 lines]
> >> tblProgramHomeDepot.ItemNumber
> >> WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])));

Reply to this Message

Allen Browne - 12 Dec 2006 06:34 GMT

Explicitly supply the zero to use for Null in the Nz().
Then wrap the whole thing in a type conversion function such as CCur(),
CLng(), or CDbl().

Since the first IIf() argument can be true only when b.onhand is less than
a.onhand (and not when either value is null), you could try this:

AdjustedSalesThisWeek: CCur( Nz( IIf( [b].[onhand] > [a].[onhand],
CalculatedSales([b].[OnHand], [a].[OnHand], [Buy Pack]),
[a].[onhand] - [b].[onhand]), 0))

(Note that the CalculateSales() function must have its arguments declared as
Variant so it can handle any null values.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a query "Q2" that depends on data generated by query "Q1". Query SQL
>is at bottom of message.
[quoted text clipped - 44 lines]
> tblProgramHomeDepot.ItemNumber
> WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])));

Reply to this Message