Simple query failing

[jal]

This should be a simple query (this is a one-table query with no joins), but I'm getting "DataType Mismatch in Query expression."
Here's the deal. Our account numbers are generally 13 digits long. The first three digits constitute the facility number (FAC). All I'm doing is extracting the first three digits of the string - and converting this to an integer. Take for example this account number

5550123456789

The facility number in this case would be 555, and I basically need to convert it to an integer. The following query succeeds (takes about 60 seconds as the table is quite large).

SELECT Account, Fac
From
(
SELECT Account, CInt(Left(account, 3)) as [Fac] from Accounts
WHERE len(Account)=13 And isNumeric(left(Account,3))
) as FacNos

The above should guarantee that the FAC number is extracted as an INTEGER. So I should be able to add a WHERE clause like this:

WHERE Fac > 300

but doing so generates the error. That is, I get the error when I run:

SELECT Account, [Fac]
From
(
SELECT Account, CInt(Left(account, 3)) as [Fac] from Accounts
WHERE len(Account)=13 And isNumeric(left(Account,3))
) as FacNos
WHERE [Fac] > 300

Most likely the root of the problem is some bad account numbers where the first three "digits" are not actually numeric. But if that's the case, shouldn't the first version of the query fail too? As I said, the first version runs fine. It's only when I add the final WHERE clause that the exception throws. Access apparently thinks there is a syntax error here? I'm lost.

 

[jal]

I'm tempted to think that IsNumeric is failing to do its job here?

 

[jal]

Ok, this is pure insanity. Just to make sure that we're dealing with pure numbers here (no letters or punctuation), I nested the query three levels deep. The innermost query guarantees we're dealing with pure numbers here - and just to make sure, I created a small sample table with only two account numbers

5550123456789
x550123456789

The 2nd account number isn't numeric, so the innermost query should eliminate it - and it does (I ran a standalone test on the inner query). So the following should succeed - and yet doesn't.

SELECT Account, Fac FROM
(
     SELECT Account, Cint(Left(Account, 3)) AS Fac FROM
     (
          SELECT Account FROM Accounts WHERE ISNUMERIC(Account)
     )      as FacNos
)
WHERE Fac > 300

If I remove the final WHERE clause, it works fine. The clause generates the "DataType Mismatch in Criteria Expression" error. What the ?@$@#$ is going on here?

 

[jal]

This doesn't work either


where cint(fac) > 300

Nor does this:

where Cdbl (fac) > 300

All the above generate the datatype mismatch error.

 

[PKJ]

I tried your queries in Access-2007 and both of them worked fine giving correct results.

SELECT Account, Fac
From
(
SELECT Account, CInt(Left(account, 3)) as [Fac] from Accounts 
WHERE len(Account)=13 And isNumeric(left(Account,3))
) as FacNos

SELECT Account, [Fac]
From
(
SELECT Account, CInt(Left(account, 3)) as [Fac] from Accounts 
WHERE len(Account)=13 And isNumeric(left(Account,3))
) as FacNos
WHERE [Fac] > 300

I used Account field as Text type with size 13. May be you have extracted it from a more complex query for simplification and problem may be lying with some other fields that you may have dropped here. Check with the data-type of Account field.

 

[Brianwarnock]

2007 obviously works differently to earlier releases, infact it works as one would expect from what PKJ says, however in 2002 SP3 I get the same as Jal, the problem is resolved by using VAL instead of or as well as Cint.

Brian

 

[jal]

2007 obviously works differently to earlier releases, infact it works as one would expect from what PKJ says, however in 2002 SP3 I get the same as Jal, the problem is resolved by using VAL instead of or as well as Cint.

Brian

I had created this version of the query for data validation (and since then relied on other means to validate it). If only I had seen your solution earlier !!!

But many thanks - you can see from my frustrated posts that the whole issue was maddening to me. I'll add your solution to my notes.