Data Type Mismatch in criteria expression

[kirkm]

Occurs when I add a Val criteria

This is OK
SELECT Val([1stWeek]) AS Expr1
FROM tblMain
WHERE (((tblMain.[Date Entered])=#11/10/1990#));

but this reports an error
SELECT Val([1stWeek]) AS Expr1
FROM tblMain
WHERE (((Val([1stWeek]))=46) AND ((tblMain.[Date Entered])=#11/10/1990#));

 

[kirkm]

Hi, I did try that, it gives another error invalid use of Null

 

[June7]

Which field is Null? Val and CInt should both error on Null.

 

[kirkm]

This shows 35 entries and both fields for all 35 are not null
SELECT tblMain.[1st Week], tblMain.[Date Entered]
FROM tblMain
WHERE (((tblMain.[Date Entered])=#11/10/1990#));

 

[June7]

Certainly a bizarre error if fields really are not Null.

Could provide db for analysis.

 

[kirkm]

Yes, I'm just trying out a few things and will report back.

 

[kirkm]

I ran a Make Table query where Date Entered])=#11/10/1990#. This created 35 records.
I than ran this query and it worked
SELECT tblMain5.[1stWeek], tblMain5.[Date Entered]
FROM tblMain5
WHERE (((Val([1stWeek]))=46) AND ((tblMain5.[Date Entered])=#11/10/1990#));
However if I revert to the first table (just changing table names in the query) it again errors.
So the query isn't the problem, the table is.

 

[kirkm]

Ok I have it. My NZ was in the wrong place. Too many bracketst to keep track of!

SELECT tblMain.[1stWeek], tblMain.[Date Entered]
FROM tblMain
WHERE (((Val(nz([1stWeek]))))=46 AND ((tblMain.[Date Entered])=#11/10/1990#));