Data type mismatch error

[Jon]

Hello all,

I'm trying the run the following query

SELECT CLng(Mid([RR_VIN_FROM],13,17)) AS ChassisFrom, CLng(Mid
([RR_VIN_TO],13,17)) AS ChassisTo, SYSTEM_RR_ATTRIBUTE.O_OID,
SYSTEM_RR_ATTRIBUTE.O_MAIN_VERSION, SYSTEM_RR_ATTRIBUTE.O_SUB_VERSION,
SYSTEM_RR_ATTRIBUTE.O_VARIANT_ID, SYSTEM_RR_ATTRIBUTE.RR_MODEL,
SYSTEM_RR_ATTRIBUTE.RR_MODEL_YEAR, SYSTEM_RR_ATTRIBUTE.RR_PIN,
SYSTEM_RR_ATTRIBUTE.RR_TERRITORY, SYSTEM_RR_ATTRIBUTE.RR_VIN_FROM,
SYSTEM_RR_ATTRIBUTE.RR_VIN_TO, SYSTEM_RR_ATTRIBUTE.RR_COUNTER,
SYSTEM_RR_ATTRIBUTE.RR_PIN_FIRST_LEVEL,
SYSTEM_RR_ATTRIBUTE.RR_PIN_QUALIFIER,
SYSTEM_RR_ATTRIBUTE.RR_PIN_SECOND_LEVEL,
SYSTEM_RR_ATTRIBUTE.RR_PIN_THIRD_LEVEL,
SYSTEM_RR_ATTRIBUTE.RR_PIN_UNIQUE,
SYSTEM_RR_ATTRIBUTE.RR_REFERENCE_PIN, SYSTEM_RR_ATTRIBUTE.RR_TYPE,
SYSTEM_RR_ATTRIBUTE.RR_EXPORT_FILENAME
FROM SYSTEM_RR_ATTRIBUTE
WHERE (((SYSTEM_RR_ATTRIBUTE.RR_MODEL)="B16") AND
((SYSTEM_RR_ATTRIBUTE.RR_MODEL_YEAR)="P") AND
((SYSTEM_RR_ATTRIBUTE.RR_PIN_FIRST_LEVEL)="62") AND
((SYSTEM_RR_ATTRIBUTE.RR_PIN_SECOND_LEVEL)="09")) and CLng(Mid
([RR_VIN_FROM],13,17)) = 1

However, I get a Data type mismatch in criteria expression error. When
I remove "and CLng(Mid([RR_VIN_FROM],13,17)) = 34521" it runs. Can
anyone please help as I need this "and" in to filter the records
further.

Thanks,

Jon

 

[John Spencer]

Probably RR_VIN_FROM is null or for another reason the substring cannot
be converted by Clng since it is not all numbers.

You might try

IIF(IsNumeric(Mid([RR_VIN_FROM],13,17)),CLng(Mid([RR_VIN_FROM],13,17)),Null)
=1


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================