dyarosh
asked on
MS Access SQL not returning what I expect
I have an MS Access database with an Employees Table. The Employees table has a field, PS58, that is a text field that contains a number or null. I'm trying to format a query that will display all the records whose PS58 value is greater than 0. I haven't been able to figure out the correct syntax for the query. The SQL 1 select statement below gives me an Invalid Use of Null error.
SQL 1
SELECT EmployeeID, EmployerID, LastName, FirstName, PS58
FROM Employees
ORDER BY LastName, FirstName
WHERE CDBL(PS58) > 0;
ASKER
I got a syntax error when I tried your query so I changed it to the following:
SELECT EmployeeID, EmployerID, LastName, FirstName, nz(PS58, ''), PS58
FROM Employees
WHERE CDBL(PS58) > 0
ORDER BY LastName, FirstName;
This query still gives me an Invalid use of Null error.
SELECT EmployeeID, EmployerID, LastName, FirstName, nz(PS58, ''), PS58
FROM Employees
WHERE CDBL(PS58) > 0
ORDER BY LastName, FirstName;
This query still gives me an Invalid use of Null error.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
SELECT EmployeeID, EmployerID, LastName, FirstName, PS58 = nz(PS58, '')
FROM Employees
WHERE CDBL(PS58) > 0
ORDER BY LastName, FirstName;
FROM Employees
WHERE CDBL(PS58) > 0
ORDER BY LastName, FirstName;
ASKER
Still invalid use of null error
sorry, I overlooked the WHERE clause part:
SELECT EmployeeID, EmployerID, LastName, FirstName, PS58 = nz(PS58, '')
FROM Employees
WHERE CDBL(nz(PS58,'')) > 0
ORDER BY LastName, FirstName;
ASKER
Now I get Data type mismatch in criteria expression
that means that you have some rows with PS58 not being numerical, right?
can you show data samples?
can you show data samples?
ASKER
I'm including the database with all of the fields removed except for Employee ID, Employer ID and PS58 field.
Test.mdb.txt
Test.mdb.txt
I cannot open that on my PC, as I don't have ms access installed @ home :(
ASKER
Here is the employee table exported to a text file
Employees.txt
Employees.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. That did the trick.
for anyone reading:
§ cdbl( null ) => raises in the invalid use of NULL
§ cdbl( '' ) => raises the Data type mismatch in criteria expression
so,
§ the nz() function ensures we don't get a NULL
§ the '0' & ... ensures we don't get a empty string, but '0' at least, and for any really numerical value, the leading '0' won't change anything
glad I could help
§ cdbl( null ) => raises in the invalid use of NULL
§ cdbl( '' ) => raises the Data type mismatch in criteria expression
so,
§ the nz() function ensures we don't get a NULL
§ the '0' & ... ensures we don't get a empty string, but '0' at least, and for any really numerical value, the leading '0' won't change anything
glad I could help
SELECT EmployeeID, EmployerID, LastName, FirstName, nz(PS58, '') PS58
FROM Employees
WHERE CDBL(PS58) > 0
ORDER BY LastName, FirstName
;