Data type mismatch in criteria expression error returned...

  • Thread starter Thread starter Rashar Sharro via AccessMonster.com
  • Start date Start date
R

Rashar Sharro via AccessMonster.com

Hi,

I am receiving the following error when I attempt to run my query with the
following criteria:

IIf([tblSales_Performance_Review]![1_Saws_Total]<>0,'*',0)

The error message that I receive is "Data type mismatch in criteria
expression"

I'm pretty sure that it has something to do with my table field being
numeric, and I'm trying to insert a special character. How can I rewrite
the expression so that I can insert the special character '*'?

Thanks
 
You can't compare a number to a string in the criteria.

You could force the field to a string using string conversion function (CStr) or
by concatenating a space to the field.

Field: MakeString: YourField & ""
Criteria: LIKE IIf([tblSales_Performance_Review].[1_Saws_Total]<>0,'*','0')

This could be slow since any index on your field cannot be used.
 
Thank you... I'm almost there.

The column that I assigned an alias to, MakeString contains the records
that I am looking for, except the numbers have an * beside it. I just want
to have the column only show the * without the number... Is that possible?

Thanks.

Field: MakeString: [1_Saws_Total] & "*"
Criteria: Like IIf([tblSales_Performance_Review].[1_Saws_Total]>0,'*','0')
 
Please ignore previous message. I modified my sql script as such:

SELECT DISTINCTROW TblSales_Performance_Review.*, tblSales_Reps.Sales_Name,
Format(TblSales_Performance_Review.Cust_Number) AS Cust_NumberE, "*" AS
MakeString
FROM (TblSales_Performance_Review INNER JOIN tblSales_Reps ON
TblSales_Performance_Review.Sales_ID = tblSales_Reps.Sales_ID) INNER JOIN
tblCustomer ON tblSales_Reps.Sales_ID = tblCustomer.Sales_ID
WHERE ((([1_Saws_Total] & "*") Like IIf([tblSales_Performance_Review].
[1_Saws_Total]>0,'*','0')))
ORDER BY Format(TblSales_Performance_Review.Cust_Number) DESC;
 
Back
Top