We help IT Professionals succeed at work.

SQL AND

APD Toronto
APD Toronto asked
on
Hi Experts,

I have the following query
SELECT tblPayers.fldPayerID, tblPayers.fldFName, tblPayers.fldLName, [fldArchived]*(-1) AS archived
FROM tblPayers
WHERE (((tblPayers.fldFName)<>"") AND ((tblPayers.fldLName)<>""))
ORDER BY tblPayers.fldPayerID;

Open in new window


My code is to exclude all records where both fName and lName are blank, but do include if either one contains something, but this query returns:

fldPayerID      fldFName      fldLName      archived
3      Tim      Cooper      0
6      A      N      1

Where, without my WHERE, I get

fldPayerID      fldFName      fldLName      archived
1      DEPOSIT            0
2      N/A            0
3      Tim      Cooper      0
4      paycheque            0
5      Envelope to Tim            0
6      A      N      1

In this case, I expect to get all 6 records with and without the WHERE.

What am I doing wrong?
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Perhaps this:

SELECT tblPayers.fldPayerID, tblPayers.fldFName, tblPayers.fldLName, [fldArchived]*(-1) AS archived
FROM tblPayers
WHERE (((tblPayers.fldFName)<>"" AND (tblPayers.fldLName)<>""))
ORDER BY tblPayers.fldPayerID;
AneeshDatabase Consultant
Top Expert 2009
Commented:
It could be NULL

SELECT tblPayers.fldPayerID, tblPayers.fldFName, tblPayers.fldLName, [fldArchived]*(-1) AS archived
FROM tblPayers
WHERE ISNULL(tblPayers.fldFName,'')<>'' AND ISNULL(tblPayers.fldLName,'')<>''
ORDER BY tblPayers.fldPayerID;
APD TorontoSoftware Developer

Author

Commented:
Scott, same results.

Aneesh, syntax error.
Top Expert 2016
Commented:
try

 SELECT tblPayers.fldPayerID, tblPayers.fldFName, tblPayers.fldLName, [fldArchived]*(-1) AS archived
FROM tblPayers
WHERE [tblPayers].[fldFName] & [tblPayers].[fldLName] & ""<>""
ORDER BY tblPayers.fldPayerID;
APD TorontoSoftware Developer

Author

Commented:
This works, but why & ""<>"" and not <> ""  ?
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
WHERE NOT (tblPayers.fldFName = "" AND tblPayers.fldLName = "")
Top Expert 2016

Commented:
that is to force the null values to a zero length string