Query - criteria on variable

[tjkalb]

I am having problems with my query. Here is the SQL:

SELECT qryFinance.SectionNo, qryFinance.AbbrevSect, qryFinance.GLAccountNo, tblFinCodesCapitalAsset.Description, qryFinance.DESCRIPTION, [highwayname] & " - " & [Project Description] AS RepDescrip, Right$([GLAccountNo],4) AS vExpense
FROM (qryFinance LEFT JOIN qryFinanceProjectNames ON qryFinance.AbbrevSect = qryFinanceProjectNames.AbbrSectionNo) INNER JOIN tblFinCodesCapitalAsset ON qryFinance.GLAccountNo = tblFinCodesCapitalAsset.Code
WHERE (((Right$([GLAccountNo],4))>=3000 And (Right$([GLAccountNo],4))<=3999));

​

I am getting a Data Type Mismatch in Criteria Expression. How do I do this? Thank you in advance for your help!:banghead:

 

[pr2-eugin]

Any possibility the Account number be Null?

 

[plog]

In your WHERE clause you are trying to compare a string to a number, thus the data mismatch.

The Right function (http://www.techonthenet.com/access/functions/string/right.php) returns a string. Those characters may all be numbers, but its a string. So when you do this:

(Right$([GLAccountNo],4))<=3999)

You are trying to compare a string to a number (3999). My advice is to store your discrete pieces of data discretely. If the last 4 characters of the GLAccountNo need to be used independent of the rest of it, you need to store those 4 characters by themselves. You shouldn't store "John Quincy Adams" in one field, you should store it in 3, because it contains 3 pieces of data (First, middle and last names).

Same thing in this case. You really have at least 2 pieces of data in the GLAccountNo, the last 4 characters and the rest. You should store that data in 2 fields so that you can work with them like you need to.

Now here's the hack fix-- multiple your Right result by 1 (to convert it to a number):

(Right$([GLAccountNo],4)*1)<=3999)

 

[tjkalb]

Nope - it always has a value. I double checked to make sure.

 

[tjkalb]

Thanks for your help. I ended up adding another field to the table, a computed field, and extracted the last 4 numbers, making sure they were an integer. Query works fine now! Thanks for giving me ideas!