[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

sql QUERY return null values when it should not be.

Hello Experts,

The query below is return records that have null address fields even though the code specifies not to.  What am I missing?  I'm using Access 2010.

Thanks,


SELECT tblFacilities.FNAME AS [Facility Name], tblCensus.CensusID AS FamilyID, tblCensus.PRPLastName AS LastName, 
tblCensus.PRPFirstName AS FirstName, Trim([PRPAddress]) AS Address, Trim([PRPCity]) AS City, Trim([PRPState]) AS State, 
Trim([PRPZip]) AS Zip, tblCensus.ResidentFirstName AS [Resident First Name], tblCensus.ResidentLastName AS [Resident Last Name], 
tblCensus.UninvolvedGuardian
FROM tblFacilities INNER JOIN tblCensus ON tblFacilities.nhid = tblCensus.NHID
WHERE (((tblCensus.Batch)=[Forms]![Switchboard]![Batch]) AND ((Trim([PRPAddress])) Is Not Null Or Not (Trim([PRPAddress]))="") 
AND ((Trim([PRPCity])) Is Not Null Or Not (Trim([PRPCity]))="") AND ((Trim([PRPState])) Is Not Null Or Not (Trim([PRPState]))="") 
AND ((Trim([PRPZip])) Is Not Null Or Not (Trim([PRPZip]))="") AND ((tblCensus.UninvolvedGuardian)<>"x" Or (tblCensus.UninvolvedGuardian) Is Null) 
AND ((tblCensus.Refused)=False) AND ((tblCensus.DoNotMailFlag)=False))
ORDER BY Trim([PRPZip]);

Open in new window

0
eshurak
Asked:
eshurak
  • 7
  • 3
  • 3
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Instead of (for example) this:

((Trim([PRPCity])) Is Not Null

Use

Nz([PRPCity], "")>""

in all places.

mx
0
 
Rey Obrero (Capricorn1)Commented:
you can start with a simple query

select * from
tablex
where Trim([PRPAddress]) & "" <>""

see if you get correct records..
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Problem is ... Trim() returns a String ... and by definition, a String cannot be Null.  So ... your test really isn't working.  

Using Nz as shown above covers both a Null case and a  Zero Length String case ("")

mx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
eshurakAuthor Commented:
Actually according to help: "If string contains Null, Null is returned."

That said both the nz and trim methods work, but both are slow so I'm still hoping for a better solution.  Seems by putting a function in the criteria we are really slowing things down.
0
 
Rey Obrero (Capricorn1)Commented:



did you try the post at http :#a36971557 ?



0
 
Rey Obrero (Capricorn1)Commented:



did you try the post at http:#a36971557 ?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
eshurak - did you try my first post?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Why are you using Trim() ?  This s/b all you need (example)

SELECT Table1.*
FROM Table1
WHERE (((Table1.FIELD1) Is Not Null));


mx
0
 
eshurakAuthor Commented:
Yes, cap.  As I said in 36971664 both solutions work but are to slow for production.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

@http:#36971697 ... not much else is left in the way of a test.

mx
0
 
eshurakAuthor Commented:
I actually need to trim to fix the data but  I'll do that in an update query elsewhere to speed things up.  Thanks.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Trim is only going to remove leading/trailing Spaces. So ... are you saying the data can be all spaces?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Pretty sure using WHERE (((Table1.FIELD1) Is Not Null)); is as fast as it's going to get.

mx
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now