Data Type Mismatch in criteria expression

[Wei]

I am getting a "Data Type Mismatch in criteria expression"
error when I tried to "sort" the results returned by a
query.

In this query, I joined the field "VEHCODE_YEAR" from a
table "table1" with the field "VehCOde_Year"
from "AVICC_CURR_MOD" table, and then I wrote the
following expression:

Expr2: IIf([AVICC_CURR_MOD]![CP/SP]="A" Or Len(Trim
([AVICC_CURR_MOD]![CP/SP]))=0,0,(IIf([SEF38]=1,4,0)+
[AVICC_CURR_MOD]![CP/SP]))

datatype for SEF38 is number.

The joined fields have the same datatype: VEHCODE_YEAR"
and "VehCode_Year" are both text. And [AVICC_CURR_MOD]!
[CP/SP] is text.

The wired thing is that this query runs and returns
results. But when I try to sort these results, it gives me
the datatype mismatch error..

Pleeease help...

Wei

 

[CSmith]

Hi,

datatype for SEF38 is number.
And [AVICC_CURR_MOD]![CP/SP] is text

Isn't that the problem since you're adding them in the last IIF statement?
You might need to use the CInt( ) around the [CP/SP] column in the last IIF
statement.

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, Von Dutch, etc - 60% off

I am getting a "Data Type Mismatch in criteria expression"
error when I tried to "sort" the results returned by a
query.

In this query, I joined the field "VEHCODE_YEAR" from a
table "table1" with the field "VehCOde_Year"
from "AVICC_CURR_MOD" table, and then I wrote the
following expression:

Expr2: IIf([AVICC_CURR_MOD]![CP/SP]="A" Or Len(Trim
([AVICC_CURR_MOD]![CP/SP]))=0,0,(IIf([SEF38]=1,4,0)+
[AVICC_CURR_MOD]![CP/SP]))

datatype for SEF38 is number.

The joined fields have the same datatype: VEHCODE_YEAR"
and "VehCode_Year" are both text. And [AVICC_CURR_MOD]!
[CP/SP] is text.

The wired thing is that this query runs and returns
results. But when I try to sort these results, it gives me
the datatype mismatch error..

Pleeease help...

Wei

 

[Wei]

Thank you Calvin for helping. But no, I don't think that's
the problem. And we can actually add text and number. For
example: "1" + 1 = 2

Wei

-----Original Message-----
Hi,

datatype for SEF38 is number.
And [AVICC_CURR_MOD]![CP/SP] is text

Isn't that the problem since you're adding them in the last IIF statement?
You might need to use the CInt( ) around the [CP/SP] column in the last IIF
statement.

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, Von Dutch, etc - 60% off

I am getting a "Data Type Mismatch in criteria expression"
error when I tried to "sort" the results returned by a
query.

In this query, I joined the field "VEHCODE_YEAR" from a
table "table1" with the field "VehCOde_Year"
from "AVICC_CURR_MOD" table, and then I wrote the
following expression:

Expr2: IIf([AVICC_CURR_MOD]![CP/SP]="A" Or Len(Trim
([AVICC_CURR_MOD]![CP/SP]))=0,0,(IIf([SEF38]=1,4,0)+
[AVICC_CURR_MOD]![CP/SP]))

datatype for SEF38 is number.

The joined fields have the same datatype: VEHCODE_YEAR"
and "VehCode_Year" are both text. And [AVICC_CURR_MOD]!
[CP/SP] is text.

The wired thing is that this query runs and returns
results. But when I try to sort these results, it gives me
the datatype mismatch error..

Pleeease help...

Wei

.

 

[CSmith]

Hi,

You're welcome!

Yes, I'm aware that you can add text and numeric columns in a query, but I'm
wondering if that might be causing a problem in your sorting effort, which
is why I suggested implmenting the CInt( ) function. It's really hard to
analyze by only seeing a code snippet of your overall picture. If this query
is feeding data into an aggregate query somewhere down the line or part of
another join somewhere down the line, then I would convert my data first
before passing it along to avoid any "Data Type Mismatch" errors along the
ride.

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, Von Dutch, etc - 60% off

Thank you Calvin for helping. But no, I don't think that's
the problem. And we can actually add text and number. For
example: "1" + 1 = 2

Wei

-----Original Message-----
Hi,

datatype for SEF38 is number.
And [AVICC_CURR_MOD]![CP/SP] is text

Isn't that the problem since you're adding them in the last IIF statement?
You might need to use the CInt( ) around the [CP/SP] column in the last IIF
statement.

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, Von Dutch, etc - 60% off

I am getting a "Data Type Mismatch in criteria expression"
error when I tried to "sort" the results returned by a
query.

In this query, I joined the field "VEHCODE_YEAR" from a
table "table1" with the field "VehCOde_Year"
from "AVICC_CURR_MOD" table, and then I wrote the
following expression:

Expr2: IIf([AVICC_CURR_MOD]![CP/SP]="A" Or Len(Trim
([AVICC_CURR_MOD]![CP/SP]))=0,0,(IIf([SEF38]=1,4,0)+
[AVICC_CURR_MOD]![CP/SP]))

datatype for SEF38 is number.

The joined fields have the same datatype: VEHCODE_YEAR"
and "VehCode_Year" are both text. And [AVICC_CURR_MOD]!
[CP/SP] is text.

The wired thing is that this query runs and returns
results. But when I try to sort these results, it gives me
the datatype mismatch error..

Pleeease help...

Wei