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

J

John T Ingato

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])));
 
T

tina

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
 
A

Allen Browne

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( .[onhand] > [a].[onhand],
CalculatedSales(.[OnHand], [a].[OnHand], [Buy Pack]),
[a].[onhand] - .[onhand]), 0))

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

John T Ingato said:
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])));
Click to expand...
 
J

John T Ingato

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

tina said:
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


John T Ingato said:
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])));
Click to expand...
Click to expand...
 
T

tina

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


John T Ingato said:
You hit it on the nose. Q1 data was left aligned. The function was
returning a variant.

tina said:
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


John T Ingato said:
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
Click to expand...
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])));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CDate results in Data Type Mismatch in Query & Excel Pivot 1
Subtract one field from another to make a third field in a query? 3
creating a combo box that uses data from another combo in that tab 1

Top