Data Type Mismatch in criteria expression (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 05:05
Joined
Oct 30, 2008
Messages
1,257
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#));
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:05
Joined
Jul 9, 2003
Messages
16,244
Try:-
Code:
WHERE (((CInt([1stWeek]))=46) AND ((tblMain.[Date Entered])=#11/10/1990#));
 

kirkm

Registered User.
Local time
Tomorrow, 05:05
Joined
Oct 30, 2008
Messages
1,257
Hi, I did try that, it gives another error invalid use of Null
 

June7

AWF VIP
Local time
Today, 08:05
Joined
Mar 9, 2014
Messages
5,423
Which field is Null? Val and CInt should both error on Null.
 

kirkm

Registered User.
Local time
Tomorrow, 05:05
Joined
Oct 30, 2008
Messages
1,257
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#));
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:05
Joined
Jul 9, 2003
Messages
16,244
Use the Nz function to change the null to zero ..
 

June7

AWF VIP
Local time
Today, 08:05
Joined
Mar 9, 2014
Messages
5,423
Certainly a bizarre error if fields really are not Null.

Could provide db for analysis.
 

kirkm

Registered User.
Local time
Tomorrow, 05:05
Joined
Oct 30, 2008
Messages
1,257
Yes, I'm just trying out a few things and will report back.
 

kirkm

Registered User.
Local time
Tomorrow, 05:05
Joined
Oct 30, 2008
Messages
1,257
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

Registered User.
Local time
Tomorrow, 05:05
Joined
Oct 30, 2008
Messages
1,257
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#));
 

Users who are viewing this thread

Top Bottom